Hi,
(I posted a similar request a few minutes ago, but the datalines in that one got shifted when pasted here. And here it is again.)
I need to assign a customer category in a new variable (Derived_Customer_Cat) to each customer record based on the first non-missing value of a variable (Customer_Cat) for all records of the same customer (Customer_ID). And the following code is a demonstration of what I have and want.
Can you help me find an efficient approach? Thank you!
Jason
data have;
input Customer_ID $1 Customer_Cat $3;
datalines;
1 A
1 A
1 A
2
2 B
2 B
3
3
3 A
3 C
3
;
run;
data want;
input Customer_ID $1 Customer_Cat $3 Derived_Customer_Cat $5;
datalines;
1 A A
1 A A
1 A A
2 B
2 B B
2 B B
3 A
3 A
3 A A
3 C A
3 A
;
run;
Here is one solution but this isn't the only way to do it. I find first non-missing value of customer_cat and rename it to derived_customer_cat and merge that dataset back with the have dataset. It gets your desired results.
proc sort data=have;
by customer_id;
run;
data want(rename=(customer_cat=derived_customer_cat));
set have;
by customer_id;
where ^missing(customer_cat);
if first.customer_id;
run;
data merged;
merge have(in=a) want;
by customer_id;
if a;
run;
Paste code and log entries is either a text box, opened using the </> icon or code box opened with the "running man" icon that appears above the message window. That way the text doesn't "get shifted". The message windows here will reformat text so one of those boxes is the way to preserve things.
Thanks to ballardw, here is the properly pasted code:
data have;
input Customer_ID $1 Customer_Cat $3;
datalines;
1 A
1 A
1 A
2
2 B
2 B
3
3
3 A
3 C
3
;
run;
data want;
input Customer_ID $1 Customer_Cat $3 Derived_Customer_Cat $5;
datalines;
1 A A
1 A A
1 A A
2 B
2 B B
2 B B
3 A
3 A
3 A A
3 C A
3 A
;
run;
Here is one solution but this isn't the only way to do it. I find first non-missing value of customer_cat and rename it to derived_customer_cat and merge that dataset back with the have dataset. It gets your desired results.
proc sort data=have;
by customer_id;
run;
data want(rename=(customer_cat=derived_customer_cat));
set have;
by customer_id;
where ^missing(customer_cat);
if first.customer_id;
run;
data merged;
merge have(in=a) want;
by customer_id;
if a;
run;
Thank you lrackley! This works.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.