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

Hi,

 

I have a patient list w/ 20+ different variables - sex, location, ethnicity, race, etc. I want to create a loop that will create tables for each one. 

 

DATA VARIABLES;
INFILE DATALINES DSD;
ROW = _N_;
INPUT VAR :$ 9.;
DATALINES;
SEX
LOCATE

ETHNICITY

RACE
;

 

PROC SQL NOPRINT;
SELECT VAR INTO:PARAMLIST
SEPARATED BY ' '
FROM VARIABLES;
QUIT;

 

PROC SQL;
CREATE TABLE &PARAMLIST AS
SELECT 

VARIABLE AS VAR
,A.VLBW_YN
,A.CMV_IND
,COUNT(*) AS COUNT
FROM PAT_LIST_FINAL A
WHERE A.VIRAL_YN = 1
GROUP BY 1,2,3
;
QUIT;

PROC TRANSPOSE DATA=VARIABLE OUT=VARIABLE2 (DROP=_NAME_);
BY VAR;
ID VLBW_YN CMV_IND;
VAR COUNT;
RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

This looks much more like a report than a data set.  Have you tried something like this:

 

proc tabulate data=pat_list_final;

class  VLBW_YN CMV_IND sex locate ethnicity race;

tables sex locate ethnicity race, vlbw_yn * cmv_ind;

run;

 

There are many ways to improve the appearance of the report.  At this point, let's at least look at the possibility that it can be done in a single step, letting SAS do all the work.

View solution in original post

4 REPLIES 4
Astounding
PROC Star

You have several issues here, beginning with the CREATE statement that lists several table names where there should only be one.  You would be much better off showing what your data looks like now, specifying whether you are looking for a data set vs. a report as the output, and illustrating what the output should look like.  It may be possible to create what you need without looping, which would certainly cut in half the time it takes to produce the result you want.

ballardw
Super User

You say "create tables for each one". Each variable? Each Patient? Each combination of some thing?

 

May I ask what you will do with those? Almost every process I have seen where people start tearing there datasets apart ends up with a a very convoluted and difficult procedure to put things back together for other purposes later.

 

It really does help to show some starting data, fake values are okay as long as they demonstrate the behavior of your actual data, and what the final result looks like.

 

If the idea is to get counts of patients by demographics you are doing a lot of extra work.

Woop122
Calcite | Level 5

I am trying to create a table of counts based on each variable.


@ballardw wrote:

You say "create tables for each one". Each variable? Each Patient? Each combination of some thing?

 

May I ask what you will do with those? Almost every process I have seen where people start tearing there datasets apart ends up with a a very convoluted and difficult procedure to put things back together for other purposes later.

 

It really does help to show some starting data, fake values are okay as long as they demonstrate the behavior of your actual data, and what the final result looks like.

 

If the idea is to get counts of patients by demographics you are doing a lot of extra work.



 I already have it done, but wanted to do it more efficiently.

 

For example

                              VLBW - Infection VLBW - No Infection.  Non-VLBW - Infection Non-VLBW - No Infection

Female

Male

 

Locate of Birth 1

Locate of Birth 2

Locate of Birth 3

 

Hispanic

Non-Hispanic

 

etc 

 

Astounding
PROC Star

This looks much more like a report than a data set.  Have you tried something like this:

 

proc tabulate data=pat_list_final;

class  VLBW_YN CMV_IND sex locate ethnicity race;

tables sex locate ethnicity race, vlbw_yn * cmv_ind;

run;

 

There are many ways to improve the appearance of the report.  At this point, let's at least look at the possibility that it can be done in a single step, letting SAS do all the work.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1144 views
  • 1 like
  • 3 in conversation