BookmarkSubscribeRSS Feed
Aman4SAS
Obsidian | Level 7

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

user24feb
Barite | Level 11

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;

Ksharp
Super User

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

Aman4SAS
Obsidian | Level 7

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 ?

data_null__
Jade | Level 19

Why not just go directly to EXCEL.

data a;
   input order period :$upcase3. area:$upcase5. sales comm;
   cards;
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 print;
  
run;
ods tagsets.excelxp file='~/compass.xml';
proc report nowd missing;
  
column period  area, (sales comm) dummy;
   define period / group order=data;
   define area / across order=data;
   define sales / display;
  
define comm / display;
  
define dummy / noprint;
  
run;
ods tagsets.excelxp close;
Aman4SAS
Obsidian | Level 7

Hi , Thanks a lot,

I have a query on it, what dummy for?

Thanks

data_null__
Jade | Level 19

Remove it and you will see.

Aman4SAS
Obsidian | Level 7

Its showing error that there is no variable associated with sales:

and can we getridof first heading "Area"?

data_null__
Jade | Level 19

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.

Aman4SAS
Obsidian | Level 7

yaa, its true , its magical.

can u please help me to getridof first header "Area". i want to remove that.

thanks

SASKiwi
PROC Star


Just add a blank label to Area:

define area / ' ' across order=data;

Ksharp
Super User

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-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!

What is Bayesian Analysis?

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.

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
  • 12 replies
  • 1508 views
  • 0 likes
  • 6 in conversation