topic Re: Creating new variables using data step in SAS Programming

Creating new variables using data step

arjunaj91 — Tue, 29 Nov 2022 14:47:39 GMT

I Have a dataset named Details with the following columns:

Date	Ind	Stacks	Nets	Start	End
01st May 2020	Y	10	4	9	13
2nd May 2020	Y	5	6
3rd May 2020	Y	4	7
4th May 2020	Y	3	9
5th May 2020	Y	2	5

I want to create the values of Start and End in the following table using previous day values. Start will be Previous day end and End will be start + nets and need to iterate this for all days using proc data step. The table needs to look like this:

Date	Ind	Stacks	Nets	Start	End
01st May 2020	Y	10	4	9	13
2nd May 2020	Y	5	6	13	19
3rd May 2020	Y	4	7	26	33
4th May 2020	Y	3	9	33	42
5th May 2020	Y	2	5	42	47

I tried the following code with no success and needs help in determining what I did wrong:

data FinalTable;
set Details ;
by Date ;
retain IND STACK NETS START END
;
if first. date then do;
IND = IND;
STACK = STACKS;
NETS = NETS;
START = START;
END = END;
end;
if last. date then do;
END =START + NETS;
output;
START=END;
end;
run;
proc print; run;

Re: Creating new variables using data step

PaigeMiller — Tue, 29 Nov 2022 14:52:18 GMT

/* UNTESTED CODE */
data want;
    set have;
    prev_end=lag(end);
    if missing(start) then start=prev_end;
    if missing(end) then end=start+nets;
    drop prev_end;
run;

If you want tested code, please provide the data as SAS data step code (instructions), and not as screen captures or Excel.

Re: Creating new variables using data step

arjunaj91 — Tue, 29 Nov 2022 15:11:50 GMT

The logic works for just one date and shows empty for the next 3 days

Re: Creating new variables using data step

PaigeMiller — Tue, 29 Nov 2022 15:13:56 GMT

Please provide data as working SAS data step code, as I explained.

Re: Creating new variables using data step

arjunaj91 — Tue, 29 Nov 2022 15:22:32 GMT

data Details;
input Date :date. IND $ STACK NETS START END ;
format date date9.;
datalines;
01May-2020 Y 10 4 9 13
02May-2020 Y 5 6
03May-2020 Y 4 7
04May-2020 Y 3 9
05May-2020 Y 2 5
;

Re: Creating new variables using data step

Tom — Tue, 29 Nov 2022 15:42:09 GMT

@arjunaj91 wrote:
data Details;
input Date :date. IND $ STACK NETS START END ;
format date date9.;
datalines;
01May-2020 Y 10 4 9 13
02May-2020 Y 5 6
03May-2020 Y 4 7
04May-2020 Y 3 9
05May-2020 Y 2 5
;

That data step does not appear to work properly.

2449  data Details;
2450  input Date :date. IND $ STACK NETS START END ;
2451  format date date9.;
2452  datalines;

NOTE: Invalid data for START in line 2455 1-10.
NOTE: Invalid data for END in line 2455 12-12.
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
2455        03May-2020 Y 4 7
Date=02MAY2020 IND=Y STACK=5 NETS=6 START=. END=. _ERROR_=1 _N_=2
NOTE: Invalid data for START in line 2457 1-10.
NOTE: Invalid data for END in line 2457 12-12.
2457        05May-2020 Y 2 5
Date=04MAY2020 IND=Y STACK=3 NETS=9 START=. END=. _ERROR_=1 _N_=3
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.DETAILS has 3 observations and 6 variables.

The last four lines only have four values on them.

Did you mean to use a data step like this instead?

data Details;
  input Date :date. IND $ STACK NETS START END ;
  format date date9.;
datalines;
01May-2020 Y 10 4 9 13
02May-2020 Y  5 6 .  .
03May-2020 Y  4 7 .  .
04May-2020 Y  3 9 .  .
05May-2020 Y  2 5 .  .
;

Re: Creating new variables using data step

PaigeMiller — Tue, 29 Nov 2022 15:45:49 GMT

data want;
    set details;
    retain newend;
    if _n_=1 then do;
        newstart=start;
        newend=end;
    end;
    else if _n_>1 then do;
        newstart=newend; 
        newend=newstart+nets; 
    end;
run;

Assumes you have fixed the code as indicated by @Tom

Re: Creating new variables using data step

Tom — Tue, 29 Nov 2022 15:45:56 GMT

It does no good to RETAIN variables that are sourced from an input dataset. First it is redundant as such variables are already not reset to missing at the start of each data step iteration (which is what RETAIN actually means). But mainly because the values are overwritten by the values read from the input data.

You need to define NEW variables to retain the calculated new values of START and END.

Re: Creating new variables using data step

arjunaj91 — Tue, 29 Nov 2022 16:09:11 GMT

Thanks a lot. Works like a charm

Re: Creating new variables using data step

arjunaj91 — Tue, 29 Nov 2022 16:12:54 GMT

Thanks, Tom!