topic Re: help in my proc sql code in SAS Programming

help in my proc sql code

knveraraju91 — Sun, 21 Oct 2018 04:42:43 GMT

Dear,

I need help in my code getting output needed . With my code i am getting first three columns only. The names of column headers for TREATEMNT A1 and TREATEMNT B1, I need to have a gap between 'TREATMENT and A1'. Thank you.

output needed

STATUS COL1 TREATMENT A1 TREATEMNT B1 Total

Responders N 5 4 9

data have;
input id trt01pn;
datalines;
1 1
2 2
3 1
4 2
5 1
6 2
7 1
8 1
9 2
;


proc sql;
create table want as 
select count(distinct id) as TREATMENT A1,'Responders' as Status, 'N' as COL1 from have where trt01pn=1;
select count(distinct id) as TREATMENT B1 from have where trt01pn=2;
select count(distinct id) as Total from have;

quit;

Re: help in my proc sql code

PeterClemmensen — Sun, 21 Oct 2018 06:13:42 GMT

What for? Reporting purposes? You should not consider such things in your actual data set.

You can control such things later on in PROC REPORT

Re: help in my proc sql code

Ksharp — Sun, 21 Oct 2018 10:33:55 GMT


options validvarname=any;
proc sql;
create table want as 
select count(distinct id) as 'TREATMENT A1'n,'Responders' as Status, 'N' as COL1 from have where trt01pn=1;
select count(distinct id) as 'TREATMENT B1'n from have where trt01pn=2;
select count(distinct id) as Total from have;

quit;

Re: help in my proc sql code

knveraraju91 — Sun, 21 Oct 2018 14:06:40 GMT

Thank you for the help. I am not getting the output I need. In the output I do not see Treatment B1 and Total columns. How to modify the code to get the all columns.

Re: help in my proc sql code

singhsahab — Sun, 21 Oct 2018 14:27:18 GMT

Hello,

below code may be helpful ..

proc sql;
create table want as
select distinct Status,COL1,TREATMENT_A1,TREATMENT_B1,sum(TREATMENT_A1,TREATMENT_B1) as total from(
select 'Responders' as Status, 'N' as COL1,id,trt01pn,
case when trt01pn=1 then count(*) end as TREATMENT_A1,
case when trt01pn=2 then count(*) end as TREATMENT_B1 from have group by trt01pn);
run;


proc print data=want;
sum total;
run;

Re: help in my proc sql code

singhsahab — Sun, 21 Oct 2018 14:51:08 GMT

Hope so below Code will match your exact requirement ...

Not able to copy and paste .. so posted as screen shot....

Re: help in my proc sql code

Reeza — Sun, 21 Oct 2018 22:43:56 GMT

Variable can’t use spaces in general. There are some ways around this, but in general it’s not recommended. Instead use labels.

Select ... as treatment_a1 label = 'Treatment A1',
Rest of query.

Then when you display the table it shows up nicely but when typing you don’t have to worry about quotes or spaces.

The other option some have shown allow you to use a space but then you need to enclose the variable in quotes and add an n after. That’s so that SAS knows when a variable name ends.

Re: help in my proc sql code

Tom — Mon, 22 Oct 2018 00:10:41 GMT

Formatting your long lines of code will make it much clearer why your output data set only has the values from the first SELECT.

proc sql;
create table want as 
  select count(distinct id) as TREATMENT A1
       , 'Responders' as Status
       , 'N' as COL1 
  from have 
  where trt01pn=1
;

select count(distinct id) as TREATMENT B1
  from have
  where trt01pn=2
;

select count(distinct id) as Total
 from have
;

quit;

So your first statement is making WANT and your next two are just writing to the output window.

Also your first statement is creating a variable names TREATMENT with a label of A1. Variable names do not have spaces in them. But you can add a label to your variable. Many procedures will display results using the label instead or or in addition to the variable name. For example you can use the LABEL option on PROC PRINT to print the label.

Re: help in my proc sql code

Ksharp — Mon, 22 Oct 2018 12:16:39 GMT

Using the following code to check output.

proc contents data=want varnum;
run;

                                                                                                                   Variables in Creation Order

                                                                                                                #    Variable        Type    Len

                                                                                                                1    TREATMENT A1    Num       8
                                                                                                                2    Status          Char     10
                                                                                                                3    COL1            Char      1

Re: help in my proc sql code

s_lassen — Mon, 22 Oct 2018 12:22:12 GMT

I think that what you want is something like this:

options validvarname=any; /* needed to have blanks in variable names */
proc sql;
  create table want as select
    'Responders' as Status,
    'N' as Col1,
    (select count(distinct id) from have where trt01pn=1) as 'Treatment A1'n,
    (select count(distinct id) from have where trt01pn=2) as 'Treatment B1'n,
    count(distinct id) as Total
  from have;
quit;