## how to get all in one row

Solved
Regular Contributor
Posts: 214

# how to get all in one row

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

```data prim;
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;```

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

## Re: how to get all in one row

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;

All Replies
New Contributor
Posts: 2

## 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;
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: 214

## Re: how to get all in one row

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

Super User
Posts: 10,570

## 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: 214

## Re: how to get all in one row

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,933

## Re: how to get all in one row

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,933

## Re: how to get all in one row

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: 10,570

## 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.

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.