SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Rhodochrosite | Level 12

I have the following data file

For each variable a, b, c I have 3 period (p1 p2 p3) reporting the value.


data have;
input time        p1a p2a p3a       p1b p2b p3b      p1c p2c p3c;
datalines;
1 1 0 -1 6 9 5 3 -9 6
2 2 3 6 0 0 0 -2 -2 -9
;


for each row, I want to count the number of positive period for A, B, C seperately.
The summary column for a , b, c is:
1 3 2
3 0 0

 

In my data, there are 100 of variables, so I cannot array 1 by 1.

 

Thanks for your help.

 

HHC

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Using arrays, the key to making this a short program is whether you can abbreviate the list of variables in the array.  For example, would this ARRAY statement define all 78 variables in the proper order?

 

array all {78} p1a -- p3z;

 

If so, the task becomes straightforward:

 

data want;

set have;

array all {78} p1a -- p3z;

array sums {26} sum1-sum26;

do _n_=1 to 26;

   sums{_n_}=0;

end;

do _n_=1 to 78;

   if all{_n_} > 0 then sums{ceil(_n_/3)} + 1;

end;

run;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

I don't have access to SAS at the moment, but something like:

data want;
  set have;
  array dat{9} p1a--p3c;
  array res{9} 1;
  do i=1 to 9;
    if dat{i} > 0 then res{i}=1;
  end;
  a=sum(res1-res3);
  b=sum(res4-res6);
  c=sum(res7-res9);
run;

or:

data want;
  set have;
  a=sum(ifn(p1a>0,1,0),ifn(p2a>0,1,0),ifn(p3a>0,1,0));
  b=sum(ifn(p1b>0,1,0),ifn(p2b>0,1,0),ifn(p3b>0,1,0));
  c=sum(ifn(p1c>0,1,0),ifn(p2c>0,1,0),ifn(p3c>0,1,0));
run;
ballardw
Super User

I would question the decision to name the variables p1a p2a and p3a if I knew that I was going to process them as a group. Names of ap1 ap2 and ap3 would then work better. You may need to provide more examples of the "100 of variables" involved as a solution for your example may not work with longer or variable length names or suffixes.

 

One approach is to create variables with the suffix as a prefix so that arrays are possible. This will do that but if the actual variables have varying length names and suffixes it will not work.

data have;
input time        p1a p2a p3a       p1b p2b p3b      p1c p2c p3c;
datalines;
1 1 0 -1 6 9 5 3 -9 6
2 2 3 6 0 0 0 -2 -2 -9
;

run;

proc transpose data=have out=trans;

run;

data temp;
   set trans;
   if _name_ ne 'time' then  _name_ = cats(substr(_name_,3),_name_);
run; 

proc transpose data=temp name=_name_ out=havetrans (drop=_name_);
run;

One suspects that a dataset with "hundreds" of similarly named variables may be the wrong structure in general and would be better served with  a Time Group (values of A B C in your example) P1 P2 P3 structure in general.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Oh, I didn't see the 100's of variables.  I agree totally, why have hundreds of variables.  Doesn't make any sense.  Any processing you do on that type of strcuture is going to be far harder.  Normalise your data, process it using a simple structure, then at teh end transpose it.  

FreelanceReinh
Jade | Level 19

Hi @hhchenfx,

 

How about this?

proc transpose data=have out=trans;
by time;
run;

data trans;
do _n_=1 to 3;
  set trans;
  _name_=substr(_name_,3);
  npos=sum(npos,col1>0);
end;
drop col1;
run;

proc transpose data=trans out=want(drop=_name_);
by time;
run;

Edit: Slightly simplified the DOW loop by using an iterative DO statement rather than a DO UNTIL statement and a counter variable.

Astounding
PROC Star

Using arrays, the key to making this a short program is whether you can abbreviate the list of variables in the array.  For example, would this ARRAY statement define all 78 variables in the proper order?

 

array all {78} p1a -- p3z;

 

If so, the task becomes straightforward:

 

data want;

set have;

array all {78} p1a -- p3z;

array sums {26} sum1-sum26;

do _n_=1 to 26;

   sums{_n_}=0;

end;

do _n_=1 to 78;

   if all{_n_} > 0 then sums{ceil(_n_/3)} + 1;

end;

run;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2766 views
  • 1 like
  • 5 in conversation