Hi, I would like to summary the variable treatment: drug, placebo of the next dataset have:
subjid treatment
1 drug
2 placebo
3 drug
4 drug
5 placebo
using this syntax:
proc sql;
select treatment, count (distinct subjid) as count
from have;
quit;
I will get :
placebo 2
drug 3
But my idea is using proc sql , i will keep these values 2, and 3 in diffrent macrovariables using proc sql with the option (into),
but i need the syntax to do it.
Any help:
Thanks in advance.
V
variabels
You can get them assigned to a series of macro variables this way, but make sure you can recreate the order to match them up later with the proper treatments.
proc sql noprint;
select count(distinct subjid) into :ntrt1 - :ntrt2
group by treatment
order by treatment
;
There are two issues with this.
First you will get a warning about grouping by a variable that is not included in the selection list.
You can either also pull out the treatments into another macro variable array (if it is useful for you), or if you do not need it just shove into a dummy macro variable to suppress the warning.
select treatment,count(distinct subjid)
into dummy,:ntrt1 - :ntrt2
group by treatment
order by treatment
;
Notice that the list of macro variables is after the INTO keyword and separated by commas just like the list of values that starts the select statement.
The other problem is how many macro variables will you need? If you are creating generic code then that might not be known in advance. You can give it an extreme value for the upperrange in the INTO clause and it will only create the macro variables that it needs. You can then reference the automatic macro variable SQLOBS to find how many groups there were.
Combining these we could create two macro variable arrays (TRTxx and NTRTxx) and the count (NTRTGRP) using this statement.
select
treatment
,count(distinct subjid)
into :trt1 - :trt200
, :ntrt1 - :ntrt200
group by treatment
order by treatment
;
%let ntrtgrp = &sqlobs ;
sorry...i missed the group
proc sql;
select treatment, count (distinct subjid) as count
from have
group by treatment;
quit;
You can get them assigned to a series of macro variables this way, but make sure you can recreate the order to match them up later with the proper treatments.
proc sql noprint;
select count(distinct subjid) into :ntrt1 - :ntrt2
group by treatment
order by treatment
;
There are two issues with this.
First you will get a warning about grouping by a variable that is not included in the selection list.
You can either also pull out the treatments into another macro variable array (if it is useful for you), or if you do not need it just shove into a dummy macro variable to suppress the warning.
select treatment,count(distinct subjid)
into dummy,:ntrt1 - :ntrt2
group by treatment
order by treatment
;
Notice that the list of macro variables is after the INTO keyword and separated by commas just like the list of values that starts the select statement.
The other problem is how many macro variables will you need? If you are creating generic code then that might not be known in advance. You can give it an extreme value for the upperrange in the INTO clause and it will only create the macro variables that it needs. You can then reference the automatic macro variable SQLOBS to find how many groups there were.
Combining these we could create two macro variable arrays (TRTxx and NTRTxx) and the count (NTRTGRP) using this statement.
select
treatment
,count(distinct subjid)
into :trt1 - :trt200
, :ntrt1 - :ntrt200
group by treatment
order by treatment
;
%let ntrtgrp = &sqlobs ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.