topic Re: Sum and determine the stnd of columns that contain a certain text for each row in SAS Programming

Sum and determine the stnd of columns that contain a certain text for each row

Melika2 — Thu, 18 Feb 2021 20:56:27 GMT

I'm providing an example of a dataset I have below:

data test;
input ID Char_Var Jan_week1 Jan_week2 Feb_week3;
datalines;
1 'prod1' 1 2 3
2 'prod2' 1 2 3
;
run;

I want to determine the total of the variables that have week in there, additionally I want to figure out the standard deviation and the change over the first and last week. However, my date variables will not always be like this.. i might have an extra week... it will change over time.

Re: Sum and determine the stnd of columns that contain a certain text for each row

Reeza — Thu, 18 Feb 2021 21:20:53 GMT

Can you provide more details?

Re: Sum and determine the stnd of columns that contain a certain text for each row

Melika2 — Thu, 18 Feb 2021 21:29:45 GMT

yes, so I want a column called total which sums up all the columns that had the variable week in them, and I want a column called stnd deviations that gives the standard deviation between the columns that had week in them, so something like this:

data test;
input ID Char_Var Jan_week1 Jan_week2 Feb_week3 total stnd ;
datalines;
1 'prod1' 1 2 3 6 1
2 'prod2' 1 2 3 6 1
;
run;

Re: Sum and determine the stnd of columns that contain a certain text for each row

ballardw — Thu, 18 Feb 2021 21:32:53 GMT

@Melika2 wrote:

I'm providing an example of a dataset I have below:

data test;
input ID Char_Var Jan_week1 Jan_week2 Feb_week3;
datalines;
1 'prod1' 1 2 3
2 'prod2' 1 2 3
;
run;

I want to determine the total of the variables that have week in there, additionally I want to figure out the standard deviation and the change over the first and last week. However, my date variables will not always be like this.. i might have an extra week... it will change over time.

It is generally poor data structure to have data in the variable name. If you really must do so with a word in the name of a variable then start the name with the piece you want to find instead of the end or buried in the middle. Week_Jan1 Week_Jan2 or some such.

You may ask why. When you have a common start to variable names then SAS will allow specific forms of lists to reference them such as Week_: the colon at the end tells SAS to use all the variables whose names start with Week_.

So the functions like SUM will total the variables (assuming they are all numeric ) with code like:

Tot = sum (of Week_:);

StdDev = std(of week_:) ;

and so on. Then it doesn't matter if you don't know, at least for these functions, what the actual names are as long as they start the same.

"first" and "last" week named the way you show them can raise a lot of questions. Like what happens when your "weeks" cross a calendar year boundary.

Generally you will in the long run find out that having the "week" as an actual date variable and the data structured as

ID <other variables> date value_variable (s)

is more flexible. A big reason is that SAS provides a fair number of functions and formats to deal with variables whose values are dates. Not so much when the value is hidden in a variable name.

Re: Sum and determine the stnd of columns that contain a certain text for each row

Reeza — Thu, 18 Feb 2021 21:35:44 GMT

total = sum(of jan_week:, of feb_week:);
stdn = std(of jan_week:, of feb_week:);

You can either list the variables manually or use short cut reference methods of colons shown above. Unfortunately SAS variable shortcuts rely on the principle of prefix not suffixes.

Re: Sum and determine the stnd of columns that contain a certain text for each row

Melika2 — Mon, 22 Feb 2021 13:11:14 GMT

This helped. Thank you!