topic Re: Help with Running a Do Loop with list of variables in SAS Programming

Help with Running a Do Loop with list of variables

Woop122 — Wed, 28 Jun 2017 22:37:35 GMT

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;

Re: Help with Running a Do Loop with list of variables

Astounding — Wed, 28 Jun 2017 23:03:30 GMT

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.

Re: Help with Running a Do Loop with list of variables

ballardw — Wed, 28 Jun 2017 23:11:43 GMT

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.

Re: Help with Running a Do Loop with list of variables

Woop122 — Wed, 28 Jun 2017 23:20:47 GMT

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

Re: Help with Running a Do Loop with list of variables

Astounding — Thu, 29 Jun 2017 00:40:59 GMT

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.