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;
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;
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;
It wont work. I get output for each group and the next condition creates in next
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')
;
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 |
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');
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.