DATA Step, Macro, Functions and more

how to get all in one row

Accepted Solution Solved
Reply
Regular Contributor
Posts: 200
Accepted Solution

how to get all in one row

I have id and when doing the below calculation i get like below

data prim;
     set adqrs1 (where=(param='Total Score'));
	 by usubjid ;
     if fasfl='Y';
     if avisitn>0;

     if parcat1 in ('GAD-7' 'WLQ Index Score' 'SDS' 'WPAI' 'WHODAS' 'DSST' 'CGI-I') then delete;

     if parcat1='PDQ-D-20' then chg_pdq=chg;
     if parcat1='PDQ-D-20' then base_pdq=base;

     if parcat1='WLQ' then chg_wlq=chg;
     if parcat1='WLQ' then base_wlq=base;

     if parcat1='QIDS-SS' then base_qids=base;
     if parcat1='CGI_S' then base_cgis=base;

     keep usubjid aage asexn chg_pdq chg_wlq avisit base_pdq base_wlq base_qids base_cgis;
run;
Attachment

Accepted Solutions
Solution
‎03-23-2018 09:13 AM
Super User
Posts: 6,537

Re: how to get all in one row

Posted in reply to Astounding

Fourth, given that you need to collapse the observations, here's a way to do it that doesn't require too much programming:

 

proc sort data=prim;

by usubjid avisitn;

run;

 

data want;

update prim (obs=0) prim;

by usubjid avisitn;

run;

View solution in original post


All Replies
Frequent Learner
Posts: 1

Re: how to get all in one row

You can always nest all the conditional manipulations under a single if statement if they follow a common condition.

Like : 

IF X =1 THEN DO; /* Below mentioned 2 lines will be processed if this condition is met */

A =B;

D=C;

END; /* make sure you're closing the do block with an End statement */

 

 



data prim; set adqrs1 (where=(param='Total Score')); by usubjid ; if fasfl='Y' and avisitn>0 ; if parcat1 in ('GAD-7' 'WLQ Index Score' 'SDS' 'WPAI' 'WHODAS' 'DSST' 'CGI-I') then delete; if parcat1='PDQ-D-20' then do;
chg_pdq=chg; base_pdq=base; end;
else if parcat1='WLQ' then do;
chg_wlq=chg; base_wlq=base; end;
else if parcat1='QIDS-SS' then base_qids=base; /*since the conditions are different we can't nest them */ else if parcat1='CGI_S' then base_cgis=base; keep usubjid aage asexn chg_pdq chg_wlq avisit base_pdq base_wlq base_qids base_cgis; run;

 

 

Regular Contributor
Posts: 200

Re: how to get all in one row

Posted in reply to abhithelucifer0

It wont work. I get output for each group and the next condition creates in next

 

Super User
Posts: 9,563

Re: how to get all in one row

You can combine all your initial conditions for the row:

if
  fasfl = 'Y' and avisitn > 0 and
  parcat1 not in ('GAD-7','WLQ Index Score','SDS','WPAI','WHODAS','DSST','CGI-I')
;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 200

Re: how to get all in one row

Posted in reply to KurtBremser

I get like the below - but i need to get in the same row for usubjid and visit and now it is coming as step wise

USUBJID AVISIT AAGE ASEXN chg_pdq base_pdq chg_wlq base_wlq base_qids base_cgis
ATWORC-CA003-001 Week 4 42 1 -8 60        
ATWORC-CA003-001 Week 8 42 1 12 60        
ATWORC-CA003-001 Week 12 42 1 6 60        
ATWORC-CA003-001 Week 26 42 1 -14 60        
ATWORC-CA003-001 Week 39 42 1 -25 60        
ATWORC-CA003-001 Week 52 42 1 -19 60        
ATWORC-CA003-001 Week 4 42 1         21  
ATWORC-CA003-001 Week 8 42 1         21  
ATWORC-CA003-001 Week 12 42 1         21  
ATWORC-CA003-001 Week 26 42 1         21  
ATWORC-CA003-001 Week 39 42 1         21  
ATWORC-CA003-001 Week 52 42 1         21  
ATWORC-CA003-001 Week 4 42 1     0,090212 20,35572    
ATWORC-CA003-001 Week 8 42 1     -1,11479 20,35572    
ATWORC-CA003-001 Week 12 42 1     -3,19786 20,35572    
ATWORC-CA003-001 Week 26 42 1     -5,73216 20,35572    
ATWORC-CA003-001 Week 39 42 1     -4,19237 20,35572    
ATWORC-CA003-001 Week 52 42 1     -2,90292 20,35572    
Super User
Posts: 6,537

Re: how to get all in one row

Several questions and comments ...

 

First, getting everything on to one row is a bad idea (the vast majority of the time).  It's usually a mistake because you have to then dissect the variable names and write a later program (that changes based on the variable names) to undo the process.  If you were on a job interview, it would be advertising that you haven't learned how to program with the data in its current form.

 

Second, are you asking a question?

 

Third, do you need a BY statement?  If not, get rid of it.  If you do need the BY statement, you need to get rid of subsetting IF statements.  When a subsetting IF deletes observations, it can delete an observation that contains 1 for first.usubjid or last.usubjid.  Subsequent reference to BY variables will be suspect.  You could alternatively use one long WHERE statement:

 

where (param='Total Score') and (fasfl='Y') and (visitn > 0) and

partcat1 not in ('GAD-7' 'WLQ Index Score' 'SDS' 'SPAI' 'WHODAS' 'DSST' 'CGI-I');

Solution
‎03-23-2018 09:13 AM
Super User
Posts: 6,537

Re: how to get all in one row

Posted in reply to Astounding

Fourth, given that you need to collapse the observations, here's a way to do it that doesn't require too much programming:

 

proc sort data=prim;

by usubjid avisitn;

run;

 

data want;

update prim (obs=0) prim;

by usubjid avisitn;

run;

Super User
Posts: 9,563

Re: how to get all in one row

Your example data step uses an informat TOD, which does not exist.

Next, PARAM never contains the string of your where condition, so no rows at all will be selected.

Your example data set is called WORK.INC, but your data step uses WORK.ADQRS1.

 

Please give us usable example data that corresponds to the program you post. And give us a more clear description of what you want to achieve.

You can post a table of the desired output, for instance.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 131 views
  • 0 likes
  • 4 in conversation