Hello Everyone,
So I have the following data that I am trying to transpose:
Obs Stat Product21 Product22 Product23 Product24
1 Frequency 66.000 277.000 . 18.000
2 Mfg_Suggested_Retail_Price_Mean 70.788 174.292 . 173.056
3 Mfg_Suggested_Retail_Price_Min 17.000 13.000 . 5.000
4 Mfg_Suggested_Retail_Price_Max 130.000 385.000 . 398.000
5 Mfg_Suggested_Retail_Price_Median 68.000 164.000 . 190.500
6 Mfg_Suggested_Retail_Price_StdDev 21.731 71.703 . 141.389
This is the code that I have so far in order to do this:
data trans (keep=Statistic Price ProductLine); /*Creating SAS data set trans*/
set orion.shoe_stats; /*Using orion.shoe_stats*/
array Prod{*} _numeric_; /*Creating array Prod*/
array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'
,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');
/*Creating StatS array with all of the different statistics
Using a DO loop as this was suggested through a website*/
do i=1 to dim(Prod);
Price=StatS{i};
Statistic=Prod{i};
output;
end;
run;
proc print data=shortrotate label;
label Price='Product';
run;
The problem that I am having is that I have to create a variable Product_Line that get the number of the product from the Product name.
I am not sure how to do this if I am supposed to put in a scan statement into my DO loop in order to scan for the number from the Product Name or what I am supposed to do.
I have tried this where I created a product_line array and then set it's value to i, but that didn't work:
data trans (keep=Statistic Price PL); /*Creating SAS data set trans*/
set orion.shoe_stats; /*Using orion.shoe_stats*/
array Prod{*} _numeric_; /*Creating array Prod*/
array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'
,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');
product_line{*} _numeric_;
/*Creating StatS array with all of the different statistics
Using a DO loop as this was suggested through a website*/
do i=1 to dim(Prod);
Price=StatS{i};
Statistic=Prod{i};
PL=product_line{i}
output;
end;
run;
proc print data=shortrotate label;
label Price='Product';
run;
If anyone has any suggestions, that would be greatly appreciated.
Here is what the final result supposed to be:
The TRANS data set
Product_
Obs Stat Line Value
1 Frequency 21 66.000
2 Frequency 22 277.000
3 Frequency 23 .
4 Frequency 24 18.000
5 Mfg_Suggested_Retail_Price_Mean 21 70.788
6 Mfg_Suggested_Retail_Price_Mean 22 174.292
7 Mfg_Suggested_Retail_Price_Mean 23 .
8 Mfg_Suggested_Retail_Price_Mean 24 173.056
9 Mfg_Suggested_Retail_Price_Min 21 17.000
10 Mfg_Suggested_Retail_Price_Min 22 13.000
11 Mfg_Suggested_Retail_Price_Min 23 .
12 Mfg_Suggested_Retail_Price_Min 24 5.000
13 Mfg_Suggested_Retail_Price_Max 21 130.000
14 Mfg_Suggested_Retail_Price_Max 22 385.000
15 Mfg_Suggested_Retail_Price_Max 23 .
Thanks,
Alisa
how about:
data have;
infile cards ;
input stat $ 33. Product21 Product22 procduct23 Product24;
cards;
Frequency 66.000 277.000 . 18.000
Mfg_Suggested_Retail_Price_Mean 70.788 174.292 . 173.056
Mfg_Suggested_Retail_Price_Min 17.000 13.000 . 5.000
Mfg_Suggested_Retail_Price_Max 130.000 385.000 . 398.000
Mfg_Suggested_Retail_Price_Median 68.000 164.000 . 190.500
Mfg_Suggested_Retail_Price_StdDev 21.731 71.703 . 141.389
;
data want (keep=stat line value);
set have;
array _p(*) pro:;
do _n_=1 to dim (_p);
line=compress(vname(_p(_n_)),'','kd');
value=_p(_n_);
output;
end;
proc print;run;
Obs stat line value
1 Frequency 21 66.000
2 Frequency 22 277.000
3 Frequency 23 .
4 Frequency 24 18.000
5 Mfg_Suggested_Retail_Price_Mean 21 70.788
6 Mfg_Suggested_Retail_Price_Mean 22 174.292
7 Mfg_Suggested_Retail_Price_Mean 23 .
8 Mfg_Suggested_Retail_Price_Mean 24 173.056
9 Mfg_Suggested_Retail_Price_Min 21 17.000
10 Mfg_Suggested_Retail_Price_Min 22 13.000
11 Mfg_Suggested_Retail_Price_Min 23 .
12 Mfg_Suggested_Retail_Price_Min 24 5.000
13 Mfg_Suggested_Retail_Price_Max 21 130.000
14 Mfg_Suggested_Retail_Price_Max 22 385.000
15 Mfg_Suggested_Retail_Price_Max 23 .
16 Mfg_Suggested_Retail_Price_Max 24 398.000
17 Mfg_Suggested_Retail_Price_Median 21 68.000
18 Mfg_Suggested_Retail_Price_Median 22 164.000
19 Mfg_Suggested_Retail_Price_Median 23 .
20 Mfg_Suggested_Retail_Price_Median 24 190.500
21 Mfg_Suggested_Retail_Price_StdDev 21 21.731
22 Mfg_Suggested_Retail_Price_StdDev 22 71.703
23 Mfg_Suggested_Retail_Price_StdDev 23 .
24 Mfg_Suggested_Retail_Price_StdDev 24 141.389
Linlin
Hi Linlin,
Unfortunately, I cannot use the first part of your program because I cannot infile my data. My data set is in my library so I have to use my data set from my library.
I am also not sure what you mean by this line:
line=compress(vname(_p(_n_)),'','kd');
What does 'kd' do?
I tried to put something like this into my code like this:
data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/
set orion.shoe_stats; /*Using orion.shoe_stats*/
array Prod{*} _numeric_; /*Creating array Prod*/
array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'
,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');
array Pline{*} _numeric_;
/*Creating StatS array with all of the different statistics
Using a DO loop as this was suggested through a website*/
do i=1 to dim(Prod);
Price=StatS{i};
Statistic=Prod{i};
Pline=compress(vname(Prod(i)),"",'kd');
output;
end;
run;
proc print data=trans label;
label Price='Product';
run;
Unfortunately, I got this in my log:
ERROR: Illegal reference to the array Pline.
Not sure where to go from here.
Thanks,
Alisa
HI Alisa,
line=compress(vname(_p(_n_)),'','kd');
What does 'kd' do?
compress(vname(_p(_n_)),'','kd') will keep the digits in a variable name. so for variables product21,procduct22,product23,product24 ,line will be 21,22,23,24.
what are the variables in your dataset orion.shoe_stats?
Hi Linlin,
This is the way the data set looks like:
Obs Stat Product21 Product22 Product23 Product24
1 Frequency 66.000 277.000 . 18.000
2 Mfg_Suggested_Retail_Price_Mean 70.788 174.292 . 173.056
3 Mfg_Suggested_Retail_Price_Min 17.000 13.000 . 5.000
4 Mfg_Suggested_Retail_Price_Max 130.000 385.000 . 398.000
5 Mfg_Suggested_Retail_Price_Median 68.000 164.000 . 190.500
6 Mfg_Suggested_Retail_Price_StdDev 21.731 71.703 . 141.389
Thanks,
Alisa
try this one:
data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/
length pline $ 2;
set orion.shoe_stats; /*Using orion.shoe_stats*/
array Prod{*} _numeric_; /*Creating array Prod*/
do i=1 to dim(Prod);
Price=prod{i};
Pline=compress(vname(Prod(i)),"",'kd');
output;
end;
run;
proc print data=trans label;
label Price='Product';
run;
It works somewhat, but I still need to include the title of the statistics to get:
The TRANS data set
Product_
Obs Stat Line Value
1 Frequency 21 66.000
2 Frequency 22 277.000
3 Frequency 23 .
4 Frequency 24 18.000
5 Mfg_Suggested_Retail_Price_Mean 21 70.788
6 Mfg_Suggested_Retail_Price_Mean 22 174.292
7 Mfg_Suggested_Retail_Price_Mean 23 .
8 Mfg_Suggested_Retail_Price_Mean 24 173.056
9 Mfg_Suggested_Retail_Price_Min 21 17.000
10 Mfg_Suggested_Retail_Price_Min 22 13.000
11 Mfg_Suggested_Retail_Price_Min 23 .
12 Mfg_Suggested_Retail_Price_Min 24 5.000
13 Mfg_Suggested_Retail_Price_Max 21 130.000
14 Mfg_Suggested_Retail_Price_Max 22 385.000
15 Mfg_Suggested_Retail_Price_Max 23 .
Thanks,
Alisa
can you post your log file? maybe the variables in the keep option don't match the variables in your dataset.
data trans (keep=Statistic Price Pline);
Hi Linlin,
This is my log file which looks okay:
1984 data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/
1985 set orion.shoe_stats;
1986 array Prod{*} _numeric_;
1987 array StatS{1:6} $50.
1987! ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Sugge
1987! sted_Retail_Price_Max'
1988 ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_St
1988! dDev');
1989 do i=1 to dim(Prod);
1990 Price=StatS{i};
1991 Statistic=Prod{i};
1992 Pline=compress(vname(Prod{i}),"",'kd');
1993 output;
1994 end;
1995 run;
NOTE: There were 6 observations read from the data set ORION.SHOE_STATS.
NOTE: The data set WORK.TRANS has 24 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
1996
1997 proc print data=shortrotate label;
1998 label Price='Product';
1999 run;
NOTE: There were 24 observations read from the data set WORK.SHORTROTATE.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time
hi ... how about TRANSPOSE instead of the array approach ...
data have;
input stat : $33. product21-product24;
cards;
Frequency 66.000 277.000 . 18.000
Mfg_Suggested_Retail_Price_Mean 70.788 174.292 . 173.056
Mfg_Suggested_Retail_Price_Min 17.000 13.000 . 5.000
Mfg_Suggested_Retail_Price_Max 130.000 385.000 . 398.000
Mfg_Suggested_Retail_Price_Median 68.000 164.000 . 190.500
Mfg_Suggested_Retail_Price_StdDev 21.731 71.703 . 141.389
;
proc transpose data=have out=want (rename=(col1=value));
by stat notsorted;
var product21-product24;
run;
data want (drop=_name_);
length line $2;
set want;
line = compress(_name_,,'kd');
run;
Hi Mike,
I wish I could use proc transpose, but I can't.
Here is my code so far which isn't working:
data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/
set orion.shoe_stats; /*Using orion.shoe_stats*/
array Prod{*} _numeric_; /*Creating array Prod*/
array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'
,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');
array Pline{*} _numeric_;
/*Creating StatS array with all of the different statistics
Using a DO loop as this was suggested through a website*/
do i=1 to dim(Prod);
Price=StatS{i};
Statistic=Prod{i};
Pline=compress(vname(Prod(i)),"",'kd');
output;
end;
run;
proc print data=trans label;
label Price='Product';
run;
I am getting an illegal reference on my Pline array.
If you have any suggestions on what to do, that would be great.
Thanks,
Alisa
hi ... I'm curious ... why can't you use TRANSPOSE (it seems as if it produces the data set you wanted)
Hi Mike,
It's for a homework assignment for an Avanced SAS training course, so I have to follow the instructions in the assignment.
Thanks,
Alisa
Take a look at the last approach suggested at: http://www.sconsig.com/sastips/tip00392.htm
It may or may not be relevant, but is definitely a way to use an array as a lookup table.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.