topic Re: Create a new Column with the first and last occurrence of date variable in each row in SAS Programming

Create a new Column with the first and last occurrence of date variable in each row

KentUmeki94 — Mon, 05 Oct 2020 03:37:44 GMT

Re: Create a new Column with the first and last occurrence of date variable in each row

ChrisNZ — Mon, 05 Oct 2020 23:22:24 GMT

This data layout is difficult to work with. Databases are not spreadsheets.

The data should be organised vertically, and the date should be in one column.

Now, to answer your question, something like this should work:

data HAVE;        
  length WK20160801 - WK20170102 8;
  array WK [*] WK20160801 -- WK20170102 ( . 3 . . 5 . ); * whatever the column names are;
run;

data WANT;
  set HAVE;
  length FIRST LAST $12;
  array WK  WK20160801 -- WK20170102; * whatever the column names are;
  do over WK;
    if WK then do;
      if FIRST=' ' then FIRST=vname(WK);
      LAST=vname(WK);
    end;
  end;
run;

Re: Create a new Column with the first and last occurrence of date variable in each row

KentUmeki94 — Thu, 08 Oct 2020 22:36:24 GMT

Hi, Thank you so much for the help. It has worked. It took me a little while to figure it out but you are a life saver. I know this is asking for a lot but is there anyway you could explain this part of the code to me and what is going on exactly?

data WANT;
  set HAVE;
  length FIRST LAST $12;
  array WK  WK20160801 -- WK20170102; * whatever the column names are;
  do over WK;
    if WK then do;
      if FIRST=' ' then FIRST=vname(WK);
      LAST=vname(WK);
    end;
  end;
run;

The first part of your code didn't seem to be needed since I imported the data from a excel file?

Re: Create a new Column with the first and last occurrence of date variable in each row

ChrisNZ — Thu, 08 Oct 2020 22:39:13 GMT

Which part is puzzling you?

Re: Create a new Column with the first and last occurrence of date variable in each row

KentUmeki94 — Thu, 08 Oct 2020 22:53:40 GMT

array WK  WK20160801 -- WK20170102;

the "--" part means all variables from WK20160801 to WK20170109?

Also the loop part.

do over WK;
    if WK then do;
      if FIRST=' ' then FIRST=vname(WK);
      LAST=vname(WK);

"do over WK" means do the loop for the Array we created before right? I am unsure.

if WK then do;
      if FIRST=' ' then FIRST=vname(WK);
      LAST=vname(WK);

This whole part I am confused to be honest.

Re: Create a new Column with the first and last occurrence of date variable in each row

ChrisNZ — Fri, 09 Oct 2020 01:30:46 GMT

data WANT;
  set HAVE;
  length FIRST LAST $12;
  array WK  WK20160801 -- WK20170102;    %* put columns in an array, in data set order;
  do over WK;                            %* loop over array ;
    if WK then do;                       %* if the array element has a value then;
      if FIRST=' ' then FIRST=vname(WK); %* if it's the first value then save it;
      LAST=vname(WK);                    %* keep/overwrite the latest value ;
    end;
  end;
run;

Does this help?