topic Re: spreading values across observations in SAS Programming

spreading values across observations

Ronein — Mon, 26 Nov 2018 06:18:16 GMT

Hello

In following raw data there are 3 fields: ID ,month, weight.

I want to check IF customer was exist in base month (1801) and give him value 1 if yes and 0 if no.

I want that value 1 or 0 will appear in all rows for the customer.

The task is to create a new column "InBaseMon" .

IF first raw of customer has month=1801 then InBaseMon=1.

IF first raw of customer has month=1801 then InBaseMon=0.

Then I want to retain the value of InBaseMon (from first row of customer to other rows of customer)

The expected values for new varaible "InBaseMon" for ID=1 are: 1 1 1

The expected values for new varaible "InBaseMon" for ID=2 are: 1 1 1

The expected values for new varaible "InBaseMon" for ID=3 are: 0 0

Data rawData;
Input ID month weight;
cards;
1 1801 100
1 1802 90
1 1803 85
2 1801 90
2 1802 94
2 1803 93
3 1802 78
3 1803 80
;
run;

Data tbl1;
set rawData;
by ID;
IF (first.ID AND month=1801) then InBaseMon=1; 
Run;

Re: spreading values across observations

Ronein — Mon, 26 Nov 2018 07:01:47 GMT

I found solution

May anyone explain the code?

Data tbl1;
set rawData;
by ID;
retain  InBaseMon ;
if first.ID then do;
	InBaseMon=0;	
	if month eq 1801 then InBaseMon=1;
end;
Run;

Re: spreading values across observations

Ronein — Mon, 26 Nov 2018 07:02:28 GMT

/*Way2*/
Data tbl1;
set rawData;
by ID;
retain InBaseMon;
if first.ID and month eq 1801 then InBaseMon=1;
if first.ID and month ne 1801 then InBaseMon=0;
Run;

Re: spreading values across observations

Shmuel — Mon, 26 Nov 2018 07:00:31 GMT

You require was

Then I want to retain the value of  InBaseMon (from first row of customer to other rows of customer)

RETAIN will hold the value through observations until it is changed by the code.

Re: spreading values across observations

Kurt_Bremser — Mon, 26 Nov 2018 08:34:06 GMT

@Ronein wrote:

I found solution

May anyone explain the code?

Data tbl1;
set rawData;
by ID;
retain  InBaseMon ; * do not set InBaseMon to missing at the start of a data step iteration;
if first.ID then do; * do this in the first row for every id group;
	InBaseMon=0;	
	if month eq 1801 then InBaseMon=1; * set inbasemon conditionally;
end;
Run;

The most compact method to set your variable is this:

data tbl1;
set rawdata;
by id;
retain inbasemon;
if first.id then inbasemon = ifn(month eq 1801,1,0);
run;