In my raw dataset I have PID that belong to one or 2 categories. K9 or K12 and K6 or K12 or just K12 or I would like to get the following records (want) when category is in K6 or K9 if there are no other records for the PID. If there is only one PID and if it is K12 category then I would like to get that only record.
One other important thing is I need to get only one record per PID. But before that I would like to get Val1 and Val2 id they exist for PID.
data temp_copy;
infile cards dlm='|' truncover ;
input subDate :mmddyy10. PID :$5. AID :$2. CurrentDept :$2. val1 :$5. val2 :$5. Category $4.;
format subDate yymmdd10.;
cards;
01/08/2021|29545|54|99|-|-|K12
01/08/2021|29545|54|99|-|P3.1|K6
01/08/2021|29545|54|99|P2.4|-|K6
01/11/2021|56545|45|99|P5.4|-|K12
01/09/2021|56545|45|99|-|-|K6
02/08/2021|56546|45|88|-|-|K12
02/08/2021|49545|33|99|P5.4|-|K6
12/21/2020|99546|56|94|-|-|K9
01/27/2021|99546|56|94|P7.4|-|K12
01/27/2021|99546|56|99|-|6.1|K9
;
Want...
29545 -01/08/2021|29545|54|99|P2.4|P3.1|K6
56545 -01/11/2021|56545|45|99|P5.4|-|K6
56546 -02/08/2021|56546|45|88|-|-|K12
49545 -02/08/2021|49545|33|99|P5.4|-|K6
99546 -01/27/2021|99546|56|99|P7.4|6.1|K9
Thank you
If you have more than one category (K6 and K12, or K9 and K12), which data do you want to get?
The last one? The larger one? The smaller one?
Also, what happens if you have one PID and the value of the category is not K12?
In your example, I think PID=49545 would be the case, but it is included in the want.
The way you wrote it, "If there is only one PID and if it is K12 category then I would like to get that only record", because it reads as not including it.
---I would like to get smaller(K6 or K9) one if there are two categories for a PID.
In case only one PID then I would like to get that record irrespective of the category.
End result- WANT dataset should have one record per PID( lowest category) and with all the non missing data for val1 and val2.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: