Hi have a dataset : like
order period area sales comm
1 jan north 45 25
1 feb north 44 44
1 Mar north 75 25
1 apr north 41 35
1 may north 65 55
1 jun north 45 15
2 jan South 45 25
2 feb South 44 44
2 Mar South 75 25
2 apr South 41 35
2 may South 65 55
2 jun South 45 15
3 jan East 45 25
3 feb East 44 44
3 Mar East 75 25
3 apr East 41 35
3 may East 65 55
3 jun East 45 15
4 jan West 45 25
4 feb West 44 44
4 Mar West 75 25
4 apr West 41 35
4 may West 65 55
4 jun West 45 15
My desire output in excel is: Variable's position order as order var
North South East West
1 jan 45 25 45 25 45 25 45 25
1 feb 44 44 44 44 44 44 44 44
1 Mar 75 25 75 25 75 25 75 25
1 apr 41 35 41 35 41 35 41 35
1 may 65 55 65 55 65 55 65 55
1 jun 45 15 45 15 45 15 45 15
please help
Hi,
/* Note not tested */
proc sort data=have out=a (keep=order period area sales);
by order period area;
run;
proc sort data=have out=b (keep=order period area comm);
by order period area;
run;
proc transpose data=a out=t_a;
by order period;
var sales;
id area;
run;
proc transpose data=b out=t_b;
by order period;
var sales;
id area;
run;
proc sql;
create table WANT as
select COALESCE(A.ORDER,B.ORDER) as ORDER,
COALESCE(A.PERIOD,B.PERIOD) as PERIOD,
A.NORTH as A_NORTH,
B.NORTH as B_NORTH,
A.SOUTH as A_SOUTH,
B.SOUTH as B_SOUTH,
A.EAST as A_EAST,
B.EAST as B_EAST,
A.WEST as A_WEST,
B.WEST as B_WEST
from T_A A
full join T_B B
on A.ORDER=B.ORDER
and A.PERIOD=B.PERIOD;
quit;
This code is less sophisticated than RW9's code because it requires a dummy variable, but it might do the job:
Data A;
Input order period $ area $ sales comm;
Datalines;
1 jan north 45 25
1 feb north 44 44
1 Mar north 75 25
1 apr north 41 35
1 may north 65 55
1 jun north 45 15
2 jan South 45 25
2 feb South 44 44
2 Mar South 75 25
2 apr South 41 35
2 may South 65 55
2 jun South 45 15
3 jan East 45 25
3 feb East 44 44
3 Mar East 75 25
3 apr East 41 35
3 may East 65 55
3 jun East 45 15
4 jan West 45 25
4 feb West 44 44
4 Mar West 75 25
4 apr West 41 35
4 may West 65 55
4 jun West 45 15
;
Run;
Proc Sort Data=A;
By Order Period Area;
Run;
Proc Transpose Data=A
Out=A_Vector;
By Order Period Area;
Var Sales Comm;
Run;
Data A_Vector;
Set A_Vector;
Area_Var=Compress(Area!!"_"!!_NAME_);
Run;
Proc Sort Data=A_Vector;
By Period;
Run;
Proc Transpose Data=A_Vector
Out=A_Trans (Drop=_NAME_);
By Period;
Var Col1;
ID Area_Var;
Run;
Assuming all the id have the same period .
Data have; Input order period $ area $ sales comm; Datalines; 1 jan north 45 25 1 feb north 44 44 1 Mar north 75 25 1 apr north 41 35 1 may north 65 55 1 jun north 45 15 2 jan South 45 25 2 feb South 44 44 2 Mar South 75 25 2 apr South 41 35 2 may South 65 55 2 jun South 45 15 3 jan East 45 25 3 feb East 44 44 3 Mar East 75 25 3 apr East 41 35 3 may East 65 55 3 jun East 45 15 4 jan West 45 25 4 feb West 44 44 4 Mar West 75 25 4 apr West 41 35 4 may West 65 55 4 jun West 45 15 ; Run; proc sql; create table temp as select distinct catt('have(rename=(sales=',area,'_sales comm=',area,'_comm) where=(order=',order,' ) )') as data from have; quit; data _null_; set temp end=last; if _n_ eq 1 then call execute('data want(drop=area) ; merge ') ; call execute(data); if last then call execute(';run;'); run;
Xia Keshan
Hi Xia,
Can u please suggest me to resolve one issue with proc sql.
I have a data with order D-C-B-A
when i run this code:
proc sql noprint;
select distinct areaname into : arealist separated by "|" from test ;
%let Ar_ct= &sqlobs;
quit;
%put &arealist &ar_ct;
its giving me arealist : A|B|C|D
I dont want to change existing order, Can it be resolve ?
Why not just go directly to EXCEL.
Hi , Thanks a lot,
I have a query on it, what dummy for?
Thanks
Remove it and you will see.
Its showing error that there is no variable associated with sales:
and can we getridof first heading "Area"?
Now you see that DUMMY is the little trick that makes it all work. It is documented somewhere and I can't remember the "reason" that PROC REPORT works that way but you can look it up.
yaa, its true , its magical.
can u please help me to getridof first header "Area". i want to remove that.
thanks
Just add a blank label to Area:
define area / ' ' across order=data;
OK. Make an order variable N .
data have; input group a $ b c; datalines; 1 A 23 20 1 B 34 32 1 C 29 12 1 D 24 10 ; run; data have; set have; if a='A' then n=4; else if a='B' then n=3; else if a='C' then n=2; else if a='D' then n=1; run; proc sql; select distinct a into : list separated by '|' from have order by n; quit; %put &list ;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.