BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

I'm new to SAS and have a simple dataset called ORIG_DATA from which I need to create a new dataset SUMMARY which shows the total by Salesman_ID by Day_ID

In essence the SUMMARY output should look like this where the numbers are the sum of totals.

Salesman_ID|Day_1|Day_2

|30   |40 

|60   |0 

|20   |70

In SQL, I'd do

Select salesman_id, sum(case when day_id=1 then total else 0 end) as day_1, sum(case when day_id=2 then total else 0 end) as day_2 from ORIG_DATA group by salesman_id

but for this problem I'm not allowed to use proc sql. How else can I do this in SAS? Haven't the foggiest at the moment. apologies for nontable format

ORIG_DATA as below

Day_ID|Salesman_ID|Other_field|total 

1   ||R000  |10 

1   ||R002  |20 

2   ||R000  |10 

2   ||R004  |30 

1   ||R002  |20 

1   ||R000  |40 

1   ||R004  |0 

2   ||R003  |40 

2   ||R004  |10 

1   ||R002  |20 

2   ||R002  |20

1 ACCEPTED SOLUTION

Accepted Solutions
AskoLötjönen
Quartz | Level 8

data have;

input Day_ID Salesman_ID $ Other_field $ total ;

cards;

1   A  R000  10

1   A  R002  20

2   A  R000  10

2   A  R004  30

1   B  R002  20

1   B  R000  40

1   B  R004  0

2   C  R003  40

2   C  R004  10

1   C  R002  20

2   C  R002  20

;

proc SUMMARY sum DATA=HAVE  nway;

  CLASS Salesman_ID Day_ID;

  VAR total;

  OUTPUT sum=total OUT=TMP1(DROP=_freq_ _type);

RUN;

PROC TRANSPOSE DATA=TMP1 OUT=SUMMARY(DROP=_NAME_) PREFIX=day_;

  VAR TOTAL;

  ID  day_id;

  BY  Salesman_ID;

RUN;

View solution in original post

2 REPLIES 2
AskoLötjönen
Quartz | Level 8

data have;

input Day_ID Salesman_ID $ Other_field $ total ;

cards;

1   A  R000  10

1   A  R002  20

2   A  R000  10

2   A  R004  30

1   B  R002  20

1   B  R000  40

1   B  R004  0

2   C  R003  40

2   C  R004  10

1   C  R002  20

2   C  R002  20

;

proc SUMMARY sum DATA=HAVE  nway;

  CLASS Salesman_ID Day_ID;

  VAR total;

  OUTPUT sum=total OUT=TMP1(DROP=_freq_ _type);

RUN;

PROC TRANSPOSE DATA=TMP1 OUT=SUMMARY(DROP=_NAME_) PREFIX=day_;

  VAR TOTAL;

  ID  day_id;

  BY  Salesman_ID;

RUN;

wanderingmanifold
Calcite | Level 5

thanks very much

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
  • 2 replies
  • 1069 views
  • 0 likes
  • 2 in conversation