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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.