Transpose datasets whith sql

[mansour_ib_sas]

Hello, 

I partage this solution of transpose dataset with sql.

What donc you think?

Thank you

option mprint mlogic symbolgen;
data test;
input id type $ count;
cards;
111 gness 45
111 brehat 90
111 bt 90
111 bat 90
112 gness 45
112 brehat 90
112 bt 90
 
; run;
 
 
proc sql;
create table test as select * 
from test order by id, type;
quit;
 
 
%macro tt;
 
proc sql noprint; 
 
select count(distinct id) into :n_id
from test;
 
select distinct id  into :col1 -:col%left(&n_id)
 
from test;
 
quit;
 
 
%do i=1 %to &n_id;
     proc sql;
     create table test1&&col&i as
     select id ,type,count , monotonic() as nbr  from test
     where id=&&col&i ; 
 
     proc sql noprint; 
     select count(id) into:n_id_r from test1&&col&i;quit;
     proc sql;
 
     proc sql;
     select distinct (type)  into :type1 -:type%left(&n_id_r)
     from  test1&&col&i;
     quit;
 
     %do j=1 %to &n_id_r;
 
            proc sql;
            create table test_1_&j._&&col&i as
            select id , count as &&type&j. from test1&&col&i
            where nbr=&j.
            ;
 
    %end;
 
 %end;
 
%mend;
 
%tt;
 
data fin ;
merge test_1_:   ;
by id;
run;

[Tom]

Putting aside WHY anyone would want to do that given that SAS already has an optimized PROC for transposing date. 

proc transpose data=test out=want (drop=_name_) ;
  by id;
  id type ;
  var count;
run;

Which generates a dataset like this:

data work.want ;
  input id gness brehat bt bat ;
datalines4;
111 45 90 90 90
112 45 90 90 .
;;;;

Let's look at your program and offer some tweaks.

Note that there is no need to start and stop PROC SQL multiple times. The statements in PROC SQL execute immediately.

There is no need to count the number of observations a SELECT is going to generate. SAS already counts that for you and puts it into the automatic macro variable SQLOBS. Also (unless you are using a really really old version of SAS) here is no need set the upper bound when generating multiple macro variables. 

into :col1 - 

If you are using an old version of SAS then just use a really large number for the upper bound.

into :col1 - :col999999

SAS will only create the macro variables it needs based on the data, no matter what upper bound you set.

Also what you want to count is the number of distinct values of TYPE and not ID, since those are the new column names you want to create.

proc sql noprint; 
select distinct type into :col1 - from test;
%let n_type=&sqlobs;

To reduce the multiple observations per ID caused by having different values of TYPE you can use an SQL aggregate function, like MAX().  Just use a CASE statement to tell it which values to consider for each output column.

create table want as
  select id
%do i=1 %to &n_type ;
  ,max(case when type = "&&col&i" then count else . end) as &&col&i
%end;
  from test
  group by 1
  order by 1
;
quit;

[novinosrin]

Also,from a production environment point of view there has been stupendous effort by @art297 and his team in creating http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

which is well worth in term of brevity and convenience. So I would request considering it.

[Reeza]

As noted, there are easier methods that should be used in SAS. For a quick code review here are some suggestions, but for a production environment you should definitely be using @Tom solution. 

proc sql noprint; 
 
select count(distinct id) into :n_id
from test;
 
select distinct id  into :col1 -:col%left(&n_id)
 
from test;
 
quit;

This is overkill, you only need :col1- SAS will fill in the last item so you don't need to create that value unless you need it. 

proc sql NOPRINT;
select distinct ID into :col1-
from ...
quit;
%let n_id = &sqlobs;

For the step:

    proc sql;
     create table test1&&col&i as
     select id ,type,count , monotonic() as nbr  from test
     where id=&&col&i ; 

 You would be better off creating a view or using a data set with a specific name. You would also want to delete these temporary data sets so they don't carry over to next runs and so that you don't end up with a huge library. 

In this step you have a count, but you should be able to capture it from the previous step using the automatic macro variable SQLOBS.

  proc sql noprint; 
     select count(id) into:n_id_r from test1&&col&i;quit;

You may also want to be careful with using naming conventions that are that close, ie 

TEST_

TEST1_

TEST_1

It's very easy to mix these up and then it becomes really hard to debug at that point. 

Hope these help with what you were trying to learn. 

And as shown, these 4 lines replace your 70 lines of code. 

As a learning exercise, one place where macros are still required - trying to output a table and graph onto the same page. There's no easy way to use BY groups or a PROC to get around that, though you can use ODS GREPLAY I believe but it's harder to implement. 

[PGStats]

"As a learning exercise, one place where macros are still required - trying to output a table and graph onto the same page. There's no easy way to use BY groups or a PROC to get around that, though you can use ODS GREPLAY I believe but it's harder to implement. "

I sometimes find ODS LAYOUT useful for the purpose of organizing many tables or graphs on the same page. It only works for some output destinations though.

[s_lassen · Accepted solution]

I think it is a rather silly idea (use PROC TRANSPOSE!), but a fun challenge. I would do it like this:

%macro SqlTrans;
  %local i types ids id set;
  proc sql noprint;
    select distinct(catx(' ',type,'FLOAT')) into :types separated by ','
    from test;
    create table fin(id FLOAT,&types);
    select distinct id into: ids separated by ' ' from test;
    %do i=1 %to &sqlobs;
      %let id=%scan(&ids,&i);
      select cats(type,'=',sum(count)) into :set separated by ','
      from test where id=&id
      group by type;
      insert into fin set id=&id,&set;
      %end;
  quit;
%mend;
%SqlTrans;

Note that this macro actually manages to declare all created variables local (you can do that with SQLOBS as well, if you want to). And that I managed to keep the whole shebang in SQL, no final data step.

I used SUM(COUNT) and GROUP BY TYPE in case there are duplicates on TYPE within an ID.