topic Re: I need help to understand proc SQl code and to do same in another way. Thanks! in New SAS User

I need help to understand proc SQl code and to do same in another way. Thanks!

shanky_44 — Wed, 13 Feb 2019 16:00:06 GMT

I have very limited understanding of Proc SQl and hoping if someone an help me out here with this code. This is a summary table code.

proc sql ;
create table cnts as

select 1 as ord, trt01an, trt01a, count (distinct USUBJID ) as cnt
from ads_1 where enrlfl ="Y" group by trt01an, trt01a
union
select 2 as ord, trt01an, trt01a, count( distinct usubjid) as cnt
from ads_1 where QFELOEYE ne "N/A" group by trt01an, trt01a
union
select 3 as ord, trt01an, trt01a, count( distinct usubjid) as cnt
from ads_1 where QFELOEYE = "N/A" group by trt01an, trt01a
union
select 6 as ord, trt01an, trt01a, count( distinct usubjid) as cnt
from ads_1 where saffl ="Y" group by trt01an, trt01a ;

quit;

Re: I need help to understand proc SQl code and to do same in another way. Thanks!

ballardw — Wed, 13 Feb 2019 16:29:56 GMT

What type of help do you need?

This is counting unique USUBJID within combinations of the variables trt01an and trt01a where different conditions are met (the WHERE) and then stacking the results in a specific order into a data set (union and "value as ord")

Re: I need help to understand proc SQl code and to do same in another way. Thanks!

shanky_44 — Wed, 13 Feb 2019 19:07:10 GMT

I was looking for an explanation for the code what is happening along if there is another way to do that without Proc SQL.

Re: I need help to understand proc SQl code and to do same in another way. Thanks!

PGStats — Wed, 13 Feb 2019 20:42:11 GMT

Unless you can assume that the 4 categories (ord = 1, 2, 3, 6) are mutually exclusive, there is no simple way to replicate this query with other tools.

You can however improve performance by replacing UNION with UNION ALL.