Dear all,
I have a 2020 product list, with detailed information. I vertically joined a 2020 list with older lists (2019,2018 etc.), so I have all the products ever produced, not just 2020 current products.
My goal is to get one observation for one ID, with all information.
2020 list file:
ID detailed info1(have more) _2020 (indicator variable to show the product is in 2020 list)
A101 Germany 1
B101 China 1
older file : ex)2019
ID 2019
A101 1
C101 1
Combined list , sorted by ID (I have it to this step)
ID detailed 1 2020 2019 2018
A101 Germany 1
A101 1
A101 1
B101 China 1
B101 1
C101 1
C101 1
PROBLEM:
I want to make it look like; (tried retain but failed)
ID detailed 1 -10 2020 2019 2018
A101 Germany 1
A101 Germany 1 1
A101 Germany 1 1 1
Then For final dataset:
then using (last.ID ?) so I have one ID obs with most information
A101 Germany 1 1 1
B101 China 1 1
C101 1 1
data product_sort; set product; by ID; if first.ID then do; if detailed_info_1 ne . then temp_1 = detailed_info_1; else if detailed_info_1 = . then detailed_info_1 = temp1; retain temp1; end; /*keep only last symbol */ run;
Thank you so much for your help in advance!
data have;
input ID $ detailed1 $ _2020 _2019 _2018 ;
cards;
A101 Germany 1 . .
A101 . . 1 .
A101 . . . 1
B101 China 1 . .
B101 . . 1 .
C101 . . 1 .
C101 . . . 1
;
data want;
update have(obs=0) have;
by id;
run;
data have;
input ID $ detailed1 $ _2020 _2019 _2018 ;
cards;
A101 Germany 1 . .
A101 . . 1 .
A101 . . . 1
B101 China 1 . .
B101 . . 1 .
C101 . . 1 .
C101 . . . 1
;
data want;
update have(obs=0) have;
by id;
run;
You can use a proc summary and do a max or proc means with a max and get the result.
proc means data = have nway missing noprint;
class ID;
var _:;
output out=want(drop=_type_ _freq_) max=;
run;
proc means data = have nway missing noprint;
class ID detailed_1 ;
var _:;
output out=want(drop=_type_ _freq_) max=;
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.