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;
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_.
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.
Thanks this works out as well. Not having to do all that coding was great.
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_.
Thank you. It worked out great as well as some of the other solutins. wow
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;
Thanks, my sql isn't up to par yet but it will be.
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;
Thanks. Gotta get my SQL up to date.
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: