Transpose then Query

[Seb_A_Sanders]

Hi there, I'm new to the SAS forums, but been using Enterprise Guide 5.1 for about a year now (soon upgrading to the latest EG).

I have a project where I transpose month by month data from vertical to horizontal, thereby creating a column for each month. Obviously this adds a new column each month, which I then have to manually add to the query that follows it.

I'm wondering whether there is any way to automatically add these new columns to my query, or alternatively, to tranpose without generating the "Source" and "Label" columns. I'm a new user so while not being afraid of using code to do this, I have zero background in coding (hoping to learn this soon though)!

Thanks in advance for any help.

[RW9]

Why do you require the transpsed table?  It is easier to work with the normalised strcuture - hence why its probably in that form already.  You have hit on eof the issues of transposed data - i.e. knowing what a table where the structure keeps changing looks like, the other issue is that eventually, you will have so many columns the data is not reviewable - I mean how many people really scroll to the right through lots of columns?

I suppose if you really had to work with this - say it was for a report (i.e not for further processing), then you could either do:

proc sql noprint;
  select distinct NAME 
  into    :V separated by " "
  from   DICTIONARY.COLUMNS
  where LIBNAME="<your_libname>"
    and  substr(MEMNAME,1,3)="ABC";
quit;

The above would give you a macro variable separated by spaces, of each variable in <your_libname>.ABCxyz - just assumed a prefix of ABC here.

Or if you need to process the data, then arrays - make sure each of your column names is prefixed the same, i.e. don't put "data" as the variable name e.g:

proc transpose data=have out=t_have prefix="COL";
  by <by_vars>;
  var <var>;
  id month;
  idlabel month;
run;

Your dataset will then look like:

COLJAN   COLMAR COL...

You can then easily refer to these variables:

data tmp; 
  set t_have;
  array months{3} col:;
  ...
run;

[data_null__]

While keeping the structure vertical is usually easier to work with you don't describe the query that follows making it rather difficult to quess how it uses the transpose data.

What are SOURCE and LABEL how are the new (month) variables named?  Do you use PROC TRANSPOSE?

The answer could be as simple as, "use a SAS Variable List" but you don't give enough information to know.

Can you show example input to transpose and output and describe the query?

[jklaverstijn]

I will leave the discussion on why to transpose to others. This is my suggestion assuming you made all the right choices

In EG you can create a query with a left outer (or in my case right) join between a table of all months and your data such that all missing monts are added to the data with all other columns missing. Then do the transpose. You should then get a var for every month. EG would create such a join as follows:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TEST AS 
   SELECT t2.month, 
          t1.dept, 
          t1.value
      FROM WORK.TEST t1
           RIGHT JOIN WORK.MONTHS t2 ON (t1.month = t2.month);
QUIT;

Hope this helps,

- Jan.