Hi, I would like to retrieve one value from one column to create a new column and keep this value constant through the new column. Here is the have and want. I appreciate any advice. Thank you!
data have;
input _NAME_ $6. COL1;
datalines;
date1 17167
s1_bm1 0.011909
s1_bm2 -0.000674
s1_bm3 -0.000645
s1_bm4 0.002035
s1_bm5 0.029314
s2_bm1 0.019645
s2_bm2 0.012246
s2_bm3 0.013749
s2_bm4 0.006763
s2_bm5 0.022934
s3_bm1 0.035394
s3_bm2 0.027839
s3_bm3 0.017082
s3_bm4 0.03592
s3_bm5 0.006751
s4_bm1 0.041027
s4_bm2 0.033723
s4_bm3 0.01814
s4_bm4 0.023475
s4_bm5 0.018217
s5_bm1 0.019427
s5_bm2 0.008693
s5_bm3 0.007499
s5_bm4 0.000978
s5_bm5 0.035601
Mkt_RF 0.014
SMB 0.001
HML -0.0011
num 1
Run;
data want;
input _NAME_ $6. COL1 date number;
datalines;
date1 17167 17167 1
s1_bm1 0.011909 17167 1
s1_bm2 -0.000674 17167 1
s1_bm3 -0.000645 17167 1
s1_bm4 0.002035 17167 1
s1_bm5 0.029314 17167 1
s2_bm1 0.019645 17167 1
s2_bm2 0.012246 17167 1
s2_bm3 0.013749 17167 1
s2_bm4 0.006763 17167 1
s2_bm5 0.022934 17167 1
s3_bm1 0.035394 17167 1
s3_bm2 0.027839 17167 1
s3_bm3 0.017082 17167 1
s3_bm4 0.03592 17167 1
s3_bm5 0.006751 17167 1
s4_bm1 0.041027 17167 1
s4_bm2 0.033723 17167 1
s4_bm3 0.01814 17167 1
s4_bm4 0.023475 17167 1
s4_bm5 0.018217 17167 1
s5_bm1 0.019427 17167 1
s5_bm2 0.008693 17167 1
s5_bm3 0.007499 17167 1
s5_bm4 0.000978 17167 1
s5_bm5 0.035601 17167 1
Mkt_RF 0.014 17167 1
SMB 0.001 17167 1
HML -0.0011 17167 1
num 1 17167 1
Run;
Ok. Here is one approach.
data have;
input _NAME_ $6. COL1;
datalines;
date1 17167
s1_bm1 0.011909
s1_bm2 -0.000674
s1_bm3 -0.000645
s1_bm4 0.002035
s1_bm5 0.029314
s2_bm1 0.019645
s2_bm2 0.012246
s2_bm3 0.013749
s2_bm4 0.006763
s2_bm5 0.022934
s3_bm1 0.035394
s3_bm2 0.027839
s3_bm3 0.017082
s3_bm4 0.03592
s3_bm5 0.006751
s4_bm1 0.041027
s4_bm2 0.033723
s4_bm3 0.01814
s4_bm4 0.023475
s4_bm5 0.018217
s5_bm1 0.019427
s5_bm2 0.008693
s5_bm3 0.007499
s5_bm4 0.000978
s5_bm5 0.035601
Mkt_RF 0.014
SMB 0.001
HML -0.0011
num 1
Run;
data _null_;
set have end=lr;
if _N_=1 then call symputx('date', COL1);
if lr then call symputx('number', COL1);
run;
data want;
set have;
retain date &date. number &number.;
run;
So the values you want to retrieve and retain in the new data set is the last record with the value 1 and the first record with the date value, correct?
Ok. Here is one approach.
data have;
input _NAME_ $6. COL1;
datalines;
date1 17167
s1_bm1 0.011909
s1_bm2 -0.000674
s1_bm3 -0.000645
s1_bm4 0.002035
s1_bm5 0.029314
s2_bm1 0.019645
s2_bm2 0.012246
s2_bm3 0.013749
s2_bm4 0.006763
s2_bm5 0.022934
s3_bm1 0.035394
s3_bm2 0.027839
s3_bm3 0.017082
s3_bm4 0.03592
s3_bm5 0.006751
s4_bm1 0.041027
s4_bm2 0.033723
s4_bm3 0.01814
s4_bm4 0.023475
s4_bm5 0.018217
s5_bm1 0.019427
s5_bm2 0.008693
s5_bm3 0.007499
s5_bm4 0.000978
s5_bm5 0.035601
Mkt_RF 0.014
SMB 0.001
HML -0.0011
num 1
Run;
data _null_;
set have end=lr;
if _N_=1 then call symputx('date', COL1);
if lr then call symputx('number', COL1);
run;
data want;
set have;
retain date &date. number &number.;
run;
Another option:
data want;
set have ;
if _n_ = 1 then set have(where=(_name_ = 'date1') rename = col1=date);
if _n_ = 1 then set have(where=(_name_ = 'num') rename = col1=num);
run;
Thank you Reeza, very neat and efficient code.
data want;
set have;
retain date ;
if _NAME_='date1' then do;
date=col1;
set have(keep=col1 rename=( col1=num)) nobs=nobs point=nobs;
end;
run;
Thank you, Novinosrin!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.