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

[KentUmeki94]

CATEGORY

PRODUCT

MEDIA

TOTAL DOLS

WK 08/01/16 (B) DOLS

WK 08/08/16 (B) DOLS

WK 08/15/16 (B) DOLS

WK 08/22/16 (B) DOLS

WK 08/29/16 (B) DOLS

WK 09/05/16 (B) DOLS

WK 09/12/16 (B) DOLS

WK 09/19/16 (B) DOLS

WK 09/26/16 (B) DOLS

WK 10/03/16 (B) DOLS

WK 10/10/16 (B) DOLS

WK 10/17/16 (B) DOLS

WK 10/24/16 (B) DOLS

WK 10/31/16 (B) DOLS

WK 11/07/16 (B) DOLS

WK 11/14/16 (B) DOLS

WK 11/21/16 (B) DOLS

WK 11/28/16 (B) DOLS

WK 12/05/16 (B) DOLS

WK 12/12/16 (B) DOLS

WK 12/19/16 (B) DOLS

WK 12/26/16 (B) DOLS

WK 01/02/17 (B) DOLS

Example

example

example

50

5

45

Example 2

example 2

example 2

45

35

10

example 3

example 3

example 3

30

20

10

What I need:

CATEGORY

PRODUCT

MEDIA

TOTAL DOLS

First Date spent

Last Date spent

WK 08/01/16 (B) DOLS

WK 08/08/16 (B) DOLS

WK 08/15/16 (B) DOLS

WK 08/22/16 (B) DOLS

WK 08/29/16 (B) DOLS

WK 09/05/16 (B) DOLS

WK 09/12/16 (B) DOLS

WK 09/19/16 (B) DOLS

WK 09/26/16 (B) DOLS

WK 10/03/16 (B) DOLS

WK 10/10/16 (B) DOLS

WK 10/17/16 (B) DOLS

WK 10/24/16 (B) DOLS

WK 10/31/16 (B) DOLS

WK 11/07/16 (B) DOLS

WK 11/14/16 (B) DOLS

WK 11/21/16 (B) DOLS

WK 11/28/16 (B) DOLS

WK 12/05/16 (B) DOLS

WK 12/12/16 (B) DOLS

WK 12/19/16 (B) DOLS

WK 12/26/16 (B) DOLS

WK 01/02/17 (B) DOLS

Example

example

example

50

08/01/16

08/29/16

5

45

Example 2

example 2

example 2

45

08/22/16

12/19/16

35

10

example 3

example 3

example 3

30

08/15/16

12/05/16

20

10

Here are the columns used above ^.

From the fifth column and on, there are dates for columns. Each row has a first and last date where dollars were spent. I am looking to create new columns called "Earliest Dollars Spent" and "Last Dollars Spent" for each row. 

What code can I write to do this? 

I am new to SAS and programming in general. It is hard to explain what I am doing but I can't show you pictures since it is private data. 

Thank you so much. 

[ChrisNZ]

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;

[KentUmeki94]

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? 

[ChrisNZ]

Which part is puzzling you?

[KentUmeki94]

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.

[ChrisNZ]

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?