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.
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
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/
I changed it to image. Hope it is ok now.
It would help if you provided the data as working SAS data step code, as I requested.
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.
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
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
Thank you so much !
Worked like a charm.
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;
My example was just an example. In reality, I have 10s of products.
Can we not ignore the product, and import it as well?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: