topic Re: Assign values to a new var by 1st valid value of another var for all records of the same custome in SAS Programming

Assign values to a new var by 1st valid value of another var for all records of the same customer

JasonL — Mon, 13 Sep 2021 14:28:08 GMT

Hi,

I need to assign a customer category in a new variable (Derived_Customer_Cat) to each customer record based on the first valid 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
4
4
4 C
4 D
4 D
4
;
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
4 C
4 C
4 C C
4 D C
4 D C
4 C
;
run;

Re: Assign values to a new var by 1st valid value of another var for all records of the same custome

PaigeMiller — Mon, 13 Sep 2021 14:33:16 GMT

... based on the first valid value ...

What is a "valid value"?

Re: Assign values to a new var by 1st valid value of another var for all records of the same custome

JasonL — Mon, 13 Sep 2021 15:12:21 GMT

I should have said "non-missing" value.

Re: Assign values to a new var by 1st valid value of another var for all records of the same custome

JasonL — Mon, 13 Sep 2021 15:16:50 GMT

Sorry the datalines for "want" got shifted. And here is what the data steps should be:

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
4
4
4 C
4 D
4 D
4
;
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
4 C
4 C
4 C C
4 D C
4 D C
4 C C
;
run;

Re: Assign values to a new var by 1st valid value of another var for all records of the same custome

Jagadishkatam — Mon, 13 Sep 2021 16:24:22 GMT

Please try

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
4  
4  
4 C
4 D
4 D
4 C
;
run;

data first;
set have;
by Customer_ID;
where Customer_Cat ne '';
if first.Customer_ID;
Derived_Customer_Cat =Customer_Cat;
keep Customer_ID Derived_Customer_Cat;
run;

data want2;
merge have(in=a) first(in=b) ;
by Customer_ID;
if a;
run;

Re: Assign values to a new var by 1st valid value of another var for all records of the same custome

JasonL — Mon, 13 Sep 2021 16:29:05 GMT

Thank you! This works.