Numeric variable to character variable conversion

Question

I'm trying to plot two sets of calculations and compare them over time. The "cohort" variable, derived from coh_asof_yyyymm from original table, were stored in the data set in numeric format (201003 for 2010 March). Now when I plot them by using proc sgplot, 4 quarters of data are crammed together. How do I change the format of this variable in order to product outputs where x-axis should be in interval of quarters?

options nofmterr;
libname backtest "/retail/mortgage/consumer/new"; 

proc sql;
create table frst_cur as
select
coh_asof_yyyymm as cohort,
sum(annual_default_occurrence* wt)/sum(wt) as dr_ct,
sum(ScorePIT_PD_2013 * wt)/sum(wt) as pd_ct_pit,
sum(ScoreTTC_PD_2013 * wt)/sum(wt) as pd_ct_ttc
from backtest.sample_frst_cur_201312bkts
group by 1;
quit;

proc sgplot data = frst_cur;
    series x = cohort y = pd_ct_pit;
    series x = cohort y = pd_ct_ttc;
    format cohort yyyyqc.;
    xaxis label = 'Cohort Date';
    yaxis label = 'Defaults';
    title 'First Mortgage Current';
run;

It's going to be hard to suggest a solution without seeing some of the data and the output. Can you upload the output and add in a few records from the FRST_CUR table? — DomPazz
– DomPazz, Commented Mar 12, 2015 at 1:36
I'm unable to upload the graph produced by the code, but the first 5 lines of output data looks something like this: — KDPinCA
– KDPinCA, Commented Mar 12, 2015 at 5:58

sushil · Accepted Answer · 2015-03-12 07:33:39Z

If i'm getting it right, i think your date is a number and not a SAS date. It's not unusual, people do store date as integers in their RDBMS tables and when SAS import data from table, it assumes it to be integer rather than date. Check out the below solution code for reference.

data testing_date_integer;
infile datalines missover;
input int_date 8.;
      /* creating another variable which would be a SAS date, based on int_date.
         we would be converting the integer date to charater and then append 
         day (01) to the charater and read using YYMMDD8. informat for SAS 
         to store the character as date
      */
      sas_date=input(cats(put(int_date,8.),'01'),yymmdd8.);

format sas_date YYQ8.;
datalines4;
200008
200009
200010
200011
200012
200101
200102
200103
200104
200105
200106
;;;;
run;
proc print data=testing_date_integer;run;

If above code show and solves you problem then i would recommend you to update you PROC SQL Code

proc sql;
create table frst_cur as
select
input(cats(put(coh_asof_yyyymm ,8.),'01'),yymmdd8.) as cohort,
.
.
.

Also, i would recommend updating the format statement for cohort in PROC SGPLOT

proc sgplot data = frst_cur;
.
.
    format cohort yyq8.;

Hope this solves your problem.

Jay Corbett · Accepted Answer · 2015-03-15 17:57:12Z

0

Using the YYMMn6. informat

data HAVE;
input DATE YYMMn6.;
format date YYQ8.;
datalines;
200008
200009
200010
200011
200012
200101
200102
200103
200104
200105
200106
;
run;
Proc Print Data=HAVE noobs; Run;

data HAVE2;
input coh_asof_yyyymm 8.;
datalines;
200008
200009
200010
200011
200012
200101
200102
200103
200104
200105
200106
;
run;

proc sql;
create table frst_cur as
select
 input(put(coh_asof_yyyymm,6.),YYMMn6.) as cohort format=YYQ8.
From HAVE2;
Quit;

answered Mar 15, 2015 at 17:57

Jay Corbett

28.5k22 gold badges59 silver badges74 bronze badges

Collectives™ on Stack Overflow

Numeric variable to character variable conversion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related