Retrieve particular number from a column

[Xusheng]

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;

[PeterClemmensen]

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?

[Xusheng]

Yes, and the first record which is “date1”##- Please type your reply above
this line. No attachments. -##

[PeterClemmensen · Accepted solution]

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;

[Reeza]

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;

[Xusheng]

Thank you Reeza, very neat and efficient code.

[novinosrin]

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;

[Xusheng]

Thank you, Novinosrin!