topic Re: New columns calculation in SAS Programming

New columns calculation

Ronein — Sun, 18 Nov 2018 09:49:08 GMT

Hello

I have 3 data sets (each data set contains data about another person).

I want to create a new data set that contains information from 3 tables and have following columns:

Score

Help_Col1 that will have following values:

For person1 : 1 1 1 1 1 1

For person2 : 2 2 2 2 2 2

For person3 : 3 3 3 3 3 3

Help_Col2 that will have following values:

for person1: 1 1 1 2 2 2

for person2: 1 1 1 2 2 2

for person3: 1 1 1 2 2 2

Data RawData_Person1;
Input Score;
Cards;
2
4
6
8
10
12
;
run;
Data RawData_Person2;
Input Score;
Cards;
3
6
9
12
15
18
;
run;
Data RawData_Person3;
Input Score;
Cards;
4
8
12
16
20
24
;
run;
data combined1 ;                                                      
  set RawData_Person: indsname = dsn ; 
  subject = scan(dsn,2); 
run ;

Re: New columns calculation

Kurt_Bremser — Sun, 18 Nov 2018 10:01:57 GMT

And what are the formulas or rules to calculate these new columns?

Re: New columns calculation

Ronein — Sun, 18 Nov 2018 10:13:17 GMT

Then for each person I need to calculate series of averages.

Average of obs 1-3

Average of obs 4-6

etc...

So...

HelpColumn1 is telling us the ID of the person

HelpColumn2 is telling us average series that it belong to for this person

Re: New columns calculation

mkeintz — Sun, 18 Nov 2018 18:05:13 GMT

It looks like helpcolumn1 is nothing more than the arbitrary assignment of a sequential ID for each incoming data set.

But I don't understand your example of helpcolumn2 values (which I presume are being displayed as rows). How is it that helpcolumn2 is an average, and in each case the sequences of averages is three 1's, followed by three 2's? Yet the displayed values of SCORE (the only incoming variable) could not produce these averages. Please clarify the calculation of helpcolulmn2. Help us help you.

Re: New columns calculation

Ronein — Sun, 18 Nov 2018 20:43:34 GMT

Thank you

column1 will get values: '1' for person 1 ,'2' for person 2 ,'3' for person 3 etc.

Column2 will get values: '1' for first 3 observations of each person ,'2' for next 3 observations of each person.

These is the requirement

Joe

Re: New columns calculation

Kurt_Bremser — Mon, 19 Nov 2018 08:40:14 GMT

So help_col2 is not an average, but a counter within a person that increments every three observations.

Retain the new columns, and use a counter for each person and the mod() function:

data combined1;                                                      
set RawData_Person: indsname = dsn;
retain help_col1 0 old_sub help_col2 counter;
subject = scan(dsn,2,'_');
if subject ne old_sub
then do;
  help_col1 + 1;
  help_col2 = 1;
  counter = 1;
  old_sub = subject;
end;
else do;
  counter + 1;
  if mod(counter,3) = 1 then help_col2 + 1;
end;
drop old_sub counter;
run;