Help with Running a Do Loop with list of variables

[Woop122]

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;

[Astounding]

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]

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]

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 · Accepted solution]

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.