BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cj9000
Obsidian | Level 7
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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

cj9000
Obsidian | Level 7

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

cj9000
Obsidian | Level 7
Sorry about how I posted this. Haven't much experience with the community yet.
ballardw
Super User

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_.

 

cj9000
Obsidian | Level 7

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

stat_sas
Ammonite | Level 13

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;

cj9000
Obsidian | Level 7

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

Steelers_In_DC
Barite | Level 11

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;

cj9000
Obsidian | Level 7


Thanks. Gotta get my SQL up to date.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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