Hi SAS Forum,
I have the below dataset.
data have;
input Acct_num date Ltr_ID $ 12-14 Sce_ID Stgy_ID;
cards;
111 201412 TLL 102 603
111 201508 TLL 101 603
222 201404 JJJ 83 245
222 201508 JJJ 83 300
;
run;
Question:
Using the above data set (over 50k records), I wanted to create the below table. Below table simply presents what are the Dates, Sce_IDs and Stgy_IDs associated with each Ltr_ID.
Ltr_ID | Date | Sce_ID | Stgy_ID |
TLL | 201412 | 102 | 603 |
201508 | 101 | ||
JJJ | 201404 | 83 | 245 |
201508 | 300 |
Could you please help me. I have used cross tabs and proc frequencies etc but found difficult. I am using sas ver 9.3.
Thanks
Mirisa
data have; input Acct_num date Ltr_ID $ 12-14 Sce_ID Stgy_ID; cards; 111 201412 TLL 102 603 111 201508 TLL 101 603 222 201404 JJJ 83 245 222 201508 JJJ 83 300 ; run; data want; set have; if Acct_num = lag(Acct_num) then call missing(Acct_num ); if Ltr_ID = lag(Ltr_ID ) then call missing(Ltr_ID ); if Sce_ID = lag(Sce_ID ) then call missing(Sce_ID ); if Stgy_ID= lag(Stgy_ID) then call missing(Stgy_ID); run;
This appears to be a job for PROC PRINT, and not a summary procedure. Are there any cases where you don't want to print every observation?
data have; input Acct_num date Ltr_ID $ 12-14 Sce_ID Stgy_ID; cards; 111 201412 TLL 102 603 111 201508 TLL 101 603 222 201404 JJJ 83 245 222 201508 JJJ 83 300 ; run; data want; set have; if Acct_num = lag(Acct_num) then call missing(Acct_num ); if Ltr_ID = lag(Ltr_ID ) then call missing(Ltr_ID ); if Sce_ID = lag(Sce_ID ) then call missing(Sce_ID ); if Stgy_ID= lag(Stgy_ID) then call missing(Stgy_ID); run;
Thanks to everybody.
Xia's approach worked well.
Thanks Xia,
Mirisa
This?
proc sort nodupkey;
by Ltr_ID Date Sce_ID Stgy_ID ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.