topic Re: how to get all in one row in SAS Programming

how to get all in one row

vraj1 — Fri, 23 Mar 2018 12:14:58 GMT

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;

Re: how to get all in one row

abhithelucifer0 — Fri, 23 Mar 2018 12:31:16 GMT

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;

Re: how to get all in one row

Kurt_Bremser — Fri, 23 Mar 2018 12:43:44 GMT

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')
;

Re: how to get all in one row

vraj1 — Fri, 23 Mar 2018 12:48:59 GMT

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

Re: how to get all in one row

Astounding — Fri, 23 Mar 2018 12:51:08 GMT

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');

Re: how to get all in one row

vraj1 — Fri, 23 Mar 2018 12:54:23 GMT

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

Re: how to get all in one row

Kurt_Bremser — Fri, 23 Mar 2018 13:00:35 GMT

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.

Re: how to get all in one row

Astounding — Fri, 23 Mar 2018 13:01:55 GMT

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;