topic Re: Transposing a table with two group levels in SAS Programming

Transposing a table with two group levels

yaniv_daaata — Mon, 25 Dec 2023 14:18:16 GMT

I have an excel table with values that depend on Product and Type.

The problem is that the first row is Product, second row is Type, and the next rows are values, depending on the first two rows.

How do I transpose it in SAS so that I can easily query on that?

Please see the example attached.

Re: Transposing a table with two group levels

PaigeMiller — Mon, 25 Dec 2023 14:11:08 GMT

Most of us will not download Excel files as they can be a security threat. Please provide (a portion of) your data as working SAS data step code (and not in any other format). Examples and instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Re: Transposing a table with two group levels

yaniv_daaata — Mon, 25 Dec 2023 14:19:22 GMT

I changed it to image. Hope it is ok now.

Re: Transposing a table with two group levels

PaigeMiller — Mon, 25 Dec 2023 14:24:34 GMT

It would help if you provided the data as working SAS data step code, as I requested.

Re: Transposing a table with two group levels

Tom — Mon, 25 Dec 2023 15:06:04 GMT

What part of that photograph is the actual data?

Do you have the data in a TEXT file, perhaps a CSV file? That would be much easier to deal with since you can write your own program to read it.

Do you have the data in a SAS dataset? What are the variable names?

Do you have the data in an Excel spreadsheet?

Do you need to do this once? Or will you get more of these files? If many files then explain what varies.

Re: Transposing a table with two group levels

yaniv_daaata — Mon, 25 Dec 2023 15:12:45 GMT

This is the actual data:

It's in an excel file.

This is the SAS table that I need:

It will need to be run periodically. Not just once.

Thank you

Re: Transposing a table with two group levels

Tom — Mon, 25 Dec 2023 15:39:41 GMT

Since the left side of your picture is not a dataset we have try and IMAGINE what dataset you actually have.

So if you ignore the first line of that block now it looks like a dataset and something PROC TRANSPOSE will handle easily.

So assuming you read from cell A2 to E8 then the variables are probably going to be named TYPE, MAIN, SUB, MAIN1, SUB1

proc transpose data=have out=want;
  by type;
run;

Then you problem is just to create your other variables from the _NAME_ variable.

data want;
  set want(rename=(type=Duration));
  length product type $8 ;
  if upcase(_name_)=:'MAIN' then type='Main'; else type='Sub';
  if indexc(_name_,'1') then product='Health'; else product='Life';
  drop _name_;
run;

Re: Transposing a table with two group levels

yaniv_daaata — Mon, 25 Dec 2023 15:44:41 GMT

My example was just an example. In reality, I have 10s of products.

Can we not ignore the product, and import it as well?

Re: Transposing a table with two group levels

Tom — Mon, 25 Dec 2023 16:01:25 GMT

Since you posted photographs instead of data let's make a dataset that looks like your data.

data have;
  input (A B C D E ) (:$8.);
cards;
Duration Life Life Health Health
Type     Main Sub  Main   Sub
1        0.9  0.8  0.7    0.8
2        1    0.9  0.8    0.9
3        1.1  1    0.9    1
;

(PROC IMPORT should generate the same thing if you use GETNAMES=NO).

Now we can transpose the two parts separately and recombine.

proc transpose data=have(obs=2) out=names ;
  by A ;
  var B--E ;
run;
proc transpose data=have(firstobs=3) out=values;
  by A ;
  var B--E ;
run;

proc sql ;
create table want as select 
  input(a.a,32.) as duration
, b.col1 as product
, c.col1 as type
, input(a.col1,32.) as value
from values a
   , names(where=(A='Duration')) b
   , names(where=(A='Type')) c
where a._name_=b._name_ and a._name_=c._name_ and b._name_=c._name_
order by 1,2,3
;
quit;

Results

OBS    duration    product    type    value

  1        1       Health     Main     0.7
  2        1       Health     Sub      0.8
  3        1       Life       Main     0.9
  4        1       Life       Sub      0.8
  5        2       Health     Main     0.8
  6        2       Health     Sub      0.9
  7        2       Life       Main     1.0
  8        2       Life       Sub      0.9
  9        3       Health     Main     0.9
 10        3       Health     Sub      1.0
 11        3       Life       Main     1.1
 12        3       Life       Sub      1.0

Re: Transposing a table with two group levels

yaniv_daaata — Mon, 25 Dec 2023 20:05:24 GMT

Thank you so much !

Worked like a charm.