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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.