CODEWITHSUNDEEP
formattingsas
KDPinCA
KDPinCA

Reputation: 65

Numeric variable to character variable conversion

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;

Upvotes: 0

Views: 127

Answers (2)

Jay Corbett
Jay Corbett

Reputation: 28441

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;

Upvotes: 0

sushil
sushil

Reputation: 1576

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.

Upvotes: 1

Related Questions