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.
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;
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?
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.