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.
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.