topic Re: problem transposing I think in SAS Programming

problem transposing I think

cj9000 — Wed, 18 Nov 2015 15:37:33 GMT

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;

Re: problem transposing I think

RW9 — Wed, 18 Nov 2015 15:55:24 GMT

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.

Re: problem transposing I think

ballardw — Wed, 18 Nov 2015 15:58:23 GMT

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

Re: problem transposing I think

stat_sas — Wed, 18 Nov 2015 16:02:52 GMT

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;

Re: problem transposing I think

Steelers_In_DC — Wed, 18 Nov 2015 16:08:46 GMT

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;

Re: problem transposing I think

cj9000 — Wed, 18 Nov 2015 16:57:00 GMT

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

Re: problem transposing I think

cj9000 — Wed, 18 Nov 2015 16:58:00 GMT

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

Re: problem transposing I think

cj9000 — Wed, 18 Nov 2015 16:59:20 GMT

Sorry about how I posted this. Haven't much experience with the community yet.

Re: problem transposing I think

cj9000 — Wed, 18 Nov 2015 17:03:13 GMT

Thanks. Gotta get my SQL up to date.

Re: problem transposing I think

cj9000 — Wed, 18 Nov 2015 17:04:31 GMT

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