SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vem
Calcite | Level 5 vem
Calcite | Level 5
Hi,
I am trying to do transpose in sas enterprise guide.
I have the data
Name case_status Case_desc count_casedesc
Cynthua 04 Obhealth 1
Cynthua 05 Diab 1
Linda 04 Bp 1
Linda 04 Diab 1
I am expecting the result as
Name Case_status obhealth Diab Bp Total
Cynthua 04 1 1
Cynthua 05 1 1
Linda 04 1 1 2


1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

You cannot directly use transpose task in EG to solve your problem as you also need Total count  column which is not coming in your input dataset.

 

Below mentioned is how to Transpose using SAS EG but again Total would not come.

Transpose Task
Set the following variables
Transpose Variable = count_casedesc
Group Analysis variable = NAME case_status
New column Name = CASE_DESC

 

 

How to get total, I have demonstrated using Base SAS code, you need to do it in EG using sql query and append task 

 

DATA HAVE;
INPUT Name $ case_status $ Case_desc $ count_casedesc;
DATALINES;
Cynthua 04 Obhealth 1
Cynthua 04 Diab 1
Cynthua 04 Bp 1
Cynthua 05 Diab 1
Linda 04 Bp 1
Linda 04 Diab 1
;
PROC SQL;
CREATE TABLE TOTAL AS
SELECT Name , case_status , "TOTAL" AS Case_desc , SUM(count_casedesc) AS count_casedesc
FROM HAVE
GROUP BY Name , case_status ;
QUIT;
DATA HAVE1;
SET HAVE TOTAL;
RUN;
PROC SORT DATA=HAVE1;
BY NAME CASE_STATUS;
RUN;
PROC TRANSPOSE DATA=HAVE1 OUT=WANT(DROP=_NAME_) ;
BY NAME case_status ;
ID CASE_DESC;
VAR count_casedesc ;
RUN;

 

View solution in original post

4 REPLIES 4
RahulG
Barite | Level 11

You cannot directly use transpose task in EG to solve your problem as you also need Total count  column which is not coming in your input dataset.

 

Below mentioned is how to Transpose using SAS EG but again Total would not come.

Transpose Task
Set the following variables
Transpose Variable = count_casedesc
Group Analysis variable = NAME case_status
New column Name = CASE_DESC

 

 

How to get total, I have demonstrated using Base SAS code, you need to do it in EG using sql query and append task 

 

DATA HAVE;
INPUT Name $ case_status $ Case_desc $ count_casedesc;
DATALINES;
Cynthua 04 Obhealth 1
Cynthua 04 Diab 1
Cynthua 04 Bp 1
Cynthua 05 Diab 1
Linda 04 Bp 1
Linda 04 Diab 1
;
PROC SQL;
CREATE TABLE TOTAL AS
SELECT Name , case_status , "TOTAL" AS Case_desc , SUM(count_casedesc) AS count_casedesc
FROM HAVE
GROUP BY Name , case_status ;
QUIT;
DATA HAVE1;
SET HAVE TOTAL;
RUN;
PROC SORT DATA=HAVE1;
BY NAME CASE_STATUS;
RUN;
PROC TRANSPOSE DATA=HAVE1 OUT=WANT(DROP=_NAME_) ;
BY NAME case_status ;
ID CASE_DESC;
VAR count_casedesc ;
RUN;

 

vem
Calcite | Level 5 vem
Calcite | Level 5
Hi,
I am trying to do transpose in sas enterprise guide.
I have the data
Name case_status Case_desc count_casedesc
Cynthua 04 Obhealth 1
Cynthua 05 Diab 1
Linda 04 Bp 1
Linda 04 Diab 1
I am expecting the result as
Name Case_status obhealth Diab Bp Total
Cynthua 04 1 1
Cynthua 05 1 1
Linda 04 1 1 2
ballardw
Super User

That looks an awful lot like a Report, especially with the "Total" you require.

If this is to be a dataset what will you do next with it? If the only thing is to print or display it then look at report tasks.

Ksharp
Super User
DATA HAVE;
INPUT Name $ case_status $ Case_desc $ count_casedesc;
DATALINES;
Cynthua 04 Obhealth 1
Cynthua 05 Diab 1
Linda 04 Bp 1
Linda 04 Diab 1
;
run;


proc transpose data=have out=temp(drop=_:);
by name case_status;
id case_desc;
var count_casedesc;
run;
data want;
 set temp;
 total=sum(of _numeric_);
run;

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 8022 views
  • 5 likes
  • 4 in conversation