DATA Step, Macro, Functions and more

problem transposing I think

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

problem transposing I think

I am trying to create a temp SAS data set containing the avg price for each stock 
and number of values used to create the avg. In addition the data set should contain
the minimum and maximum price for each stock. A listing of the data should look like below.
The code I have used is below the listing.
Thanks in advance.
Charles

Listing of Summary

Symbol

 

Price_Mean

Price_N

Price_Min

Price_Max

AVID

41.2500

1

41.25

41.25

BAC

51.0400

5

49.90

52.10

CSCO

20.3125

4

19.75

21.00

IBM

77.8000

5

75.00

81.00

LU

2.5400

2

2.53

2.55


data stockprices; infile datalines; length Symbol $ 4; input Symbol $ @; do Date = '01jan2007'd to '05jan2007'd; input Price @; if not missing(Price) then output; end; format Date mmddyy10.; datalines; CSCO 19.75 20 20.5 21 . IBM 76 78 75 79 81 LU 2.55 2.53 . . . AVID 41.25 . . . . BAC 51 51 51.2 49.9 52.1 ; /* proc print data=stockprices; run;*/ proc sort data=stockprices out=sorted; by symbol date; run; /* proc print data=sorted; run;*/ proc means data=sorted nway noprint; class Symbol; output out=symbols; run; /* proc print data=symbols; run;*/ proc transpose data=symbols prefix=stock out=oneper; by symbol; run; proc print data=oneper; run;

 


Accepted Solutions
Solution
‎11-18-2015 11:45 AM
Super User
Posts: 10,500

Re: problem transposing I think

If the only purpose of the dataset is to print then skip all the manipulation an go directly to one of the report procedures such as Proc Report or Tabulate.

proc tabulate data= stockprices;
   class symbol;
   var price;
   table symbol, price*(mean*f=f6.2 n min*f=f6.2 max*f=f6.2);
run;

Or use Proc Summary instead of means, the default output is somewhat different.

 

proc summary data=sorted nway ;
   class Symbol;
   var price;
   output out=symbols mean= n= min= max= /autoname;
run;

No need to sort before either Proc Means or Proc Summary to use the class option. The output will be sorted by the class variables within _type_.

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: problem transposing I think

[ Edited ]

For future posts, can you just use the normal area for posting text, and the code window for code.  Its difficult to read having to scroll across.  As for your point, you can get the output you want by a small change to the means code:

proc means data=sorted nway noprint;
   class Symbol;
   output out=symbols n=price_n mean=price_mean ...;
run;

If you put the elements on the output statement line, they will appear as variables rather than data rows (which is the default).  So statistic=variable name.  You wont need the transpose then.

Contributor
Posts: 27

Re: problem transposing I think

Thanks this works out as well. Not having to do all that coding was great.

Contributor
Posts: 27

Re: problem transposing I think

Sorry about how I posted this. Haven't much experience with the community yet.
Solution
‎11-18-2015 11:45 AM
Super User
Posts: 10,500

Re: problem transposing I think

If the only purpose of the dataset is to print then skip all the manipulation an go directly to one of the report procedures such as Proc Report or Tabulate.

proc tabulate data= stockprices;
   class symbol;
   var price;
   table symbol, price*(mean*f=f6.2 n min*f=f6.2 max*f=f6.2);
run;

Or use Proc Summary instead of means, the default output is somewhat different.

 

proc summary data=sorted nway ;
   class Symbol;
   var price;
   output out=symbols mean= n= min= max= /autoname;
run;

No need to sort before either Proc Means or Proc Summary to use the class option. The output will be sorted by the class variables within _type_.

 

Contributor
Posts: 27

Re: problem transposing I think

Thank you. It worked out great as well as some of the other solutins. wow

Trusted Advisor
Posts: 1,204

Re: problem transposing I think

proc sql;
create table want as
select symbol,Avg(price) as Price_Mean,count(*) as Price_N, Min(Price) as Price_Min,
max(price) as Price_Max
from stockprices
group by symbol;
quit;

Contributor
Posts: 27

Re: problem transposing I think

Thanks, my sql isn't up to par yet but it will be. 

Valued Guide
Posts: 858

Re: problem transposing I think

Here is another solution:

 

proc sql;
create table want as
select distinct
symbol,
mean(price) as Price_Mean,
count(price) as Price_N,
min(price) as Price_Min,
max(price) as Price_Max
from stockprices
group by symbol;

Contributor
Posts: 27

Re: problem transposing I think


Thanks. Gotta get my SQL up to date.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 308 views
  • 4 likes
  • 5 in conversation