topic Re: Creating a new column with dollars spent on different column in SAS Programming

Creating a new column with dollars spent on different column

KentUmeki94 — Sun, 20 Dec 2020 05:49:19 GMT

This is the code I have written so far but it does not work. There are a bunch of columns from different weeks named WK080116 to WK042720, these columns include how many dollars spent on that week. I am looking to create a new column called "spending_in_week_0" which is how many dollars spent on the week of its release. I have a column called Release_week which has the release of the product on that week. Release_week column has variables such as 080116.

So I tried to make a code that will input the dollars spent on the release day to a column called Spending_in_week_0. But unfortunately, my code does not work.

Product	WK080116	WK080816	WK081516	WKetc	WKetc	WKetc	WK042720	Release_week	Spending_in_week_0 (I need this variable)
1	$50							080116	50
2		$20						080816	$20
3			$70					081516	$70

I need results that will create a new column called Spending in Week 0 (release week), that will give me the dollar amount spent on that week.

DATA Assignment2_new_data_merged3;
set Assignment2_new_data_merged2;
array WK WK080116 -- WK042720;
do over WK;
if Release_week = substr(vname(WK),3,6) then Spending_in_week_0 = WK;
end;
run;

Please help me to create column Spending_in_week_0 please.

I would really appreciate it.

Thank you so much.

Re: Creating a new column with dollars spent on different column

PeterClemmensen — Sun, 20 Dec 2020 07:33:20 GMT

Try this

data have;
input Product WK080116 WK080816 WK081516 Release_week $;
infile datalines dlm=',' dsd;
datalines;
1,50,,,080116
2,,20,,080816
3,,,70,081516
;

data want;
   set have;
   Spending_in_week_0 = input(left(vvaluex(cats('WK', Release_week))), 8.);
run;

Re: Creating a new column with dollars spent on different column

Tom — Sun, 20 Dec 2020 18:00:25 GMT

Does your real data have only one value per observation like in your example?

If so then just take the sum of all of the variables rather than trying to figure out how to access the specific one.

Spending_in_week_0 = sum( of wk:);

Re: Creating a new column with dollars spent on different column

s_lassen — Mon, 21 Dec 2020 07:43:12 GMT

The code you have shown seems to create the data you show as needed.

What is the problem?

If it is that you have more than one week's spending in some observations, you just need to output each row:

DATA Assignment2_new_data_merged3;
  set Assignment2_new_data_merged2;
  array WK WK080116 -- WK042720;
  do over WK;
  if Release_week = substr(vname(WK),3,6) then do;
    Spending_in_week_0 = WK;
    output;
    end;
end;
run;