help in my proc sql code

[knveraraju91]

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;

[PeterClemmensen]

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

[Ksharp]

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;

[knveraraju91]

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. 

[singhsahab]

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;

[singhsahab]

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

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

[Ksharp]

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

[Reeza]

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.

[Tom]

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.

[s_lassen]

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;