topic Re: Assign values to a var by 1st non-missing value of another var for all records of the same custo in SAS Programming

Assign values to a var by 1st non-missing value of another var for all records of the same customer

JasonL — Mon, 13 Sep 2021 15:28:23 GMT

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;

Re: Assign values to a var by 1st non-missing value of another var for all records of the same custo

ballardw — Mon, 13 Sep 2021 15:43:39 GMT

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.

Re: Assign values to a var by 1st non-missing value of another var for all records of the same custo

JasonL — Mon, 13 Sep 2021 15:48:53 GMT

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;

Re: Assign values to a var by 1st non-missing value of another var for all records of the same custo

tarheel13 — Mon, 13 Sep 2021 17:04:36 GMT

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;

Re: Assign values to a var by 1st non-missing value of another var for all records of the same custo

JasonL — Mon, 13 Sep 2021 20:25:09 GMT

Thank you lrackley! This works.