topic Re: Macro Outputting Extra Rows in SAS Programming

Macro Outputting Extra Rows

djbateman — Fri, 20 Oct 2017 16:24:22 GMT

Okay. I have been racking my brain over this all day. I need some insight here.

For simplicity, I have a table with a list of subjects and the visits for each subject. I have another table of timepoints that lists the timepoints for each specific visits:

data timepoints;
	length visitnum 8 ptpt $12 egnum 8;
	input visitnum ptpt $ egnum;
	cards;
30001	PREDOSE			0.1
30001	PREDOSE			0.2
30001	PREDOSE			0.3
30001	4HR_POSTDOSE	4.0
30015	PREDOSE			0.0
30015	4HR_POSTDOSE	4.0
;
run;
data subj;
	length subjid $11 visitnum 8;
	input subjid $ visitnum;
	cards;
101-001-001	10001
101-001-001	30001
101-001-001	30015
101-001-001	30029
101-001-001	30043
101-001-001	70001
101-003-003	10001
101-003-003	20001
101-003-003	30001
101-003-003	30015
101-003-003	30029
101-003-003	30043
101-003-003	70001
101-003-004	10001
101-003-004	30001
101-003-004	30015
101-003-004	30029
101-003-004	30043
101-003-004	70001
;
run;

I need to generalize a program to expand the rows for each subject/visit based on number of timepoints for each visit (the timepoints table will be different for every study). I have been trying to create a macro to do this, but I can't seem to get it to work. I keep getting an extra row for VISITNUM=30001 and EGNUM=4. I'm not sure why. Can someone see why it runs one of the loops twice? This is the macro:

%macro expand();
	proc sql;
		select compress(put(count(*),best.)) into :ntmpts from timepoints;
		select visitnum into :visit1-:visit&ntmpts. from timepoints;
		select ptpt into :ptpt1-:ptpt&ntmpts. from timepoints;
		select egnum into :egnum1-:egnum&ntmpts. from timepoints;
	quit;

	data subj_expand;
		length ptpt $80;
		set subj;
		%do i=1 %to &ntmpts.;
			if visitnum=&&visit&i. then do;
				ptpt="&&ptpt&i.";
				egnum=&&egnum&i.;
				output;
			end;
		%end;
		else output;
	run;
%mend expand;
%expand;

Re: Macro Outputting Extra Rows

Reeza — Fri, 20 Oct 2017 16:28:33 GMT

What do you want as output?

Re: Macro Outputting Extra Rows

djbateman — Fri, 20 Oct 2017 16:41:04 GMT

I have attached an Excel file showing both what my output is and what I want it to look like. I have highlighted the extra rows in the table that is not what I want.

Re: Macro Outputting Extra Rows

ballardw — Fri, 20 Oct 2017 19:02:18 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Macro Outputting Extra Rows

ballardw — Fri, 20 Oct 2017 19:21:41 GMT

@djbateman wrote:

I keep getting an extra row for VISITNUM=30001 and EGNUM=4. I'm not sure why. Can someone see why it runs one of the loops twice? This is the macro:

The Else output is only the else when &I = &ntmpts.

in effect you are doing:

if value =1 then do; <something>; end;

if value =2 then do; <something>; end;

if value =3 then do; <something>; end;

if value =4 then do; <something>; end;

else do; <something>; end;

I might suggest building a SELECT block to only have one "else"

select (visitnum);

%do I = 1 %to &ntmpts;

when (&&visit&i.) do;

ptpt="&&ptpt&i.";

egnum=&&egnum&i.;

output;

end;

%end;

otherwise output;

end;/* the select block*/

Re: Macro Outputting Extra Rows

djbateman — Fri, 20 Oct 2017 19:36:17 GMT

Thanks, but I don't necessarily need my table in a SAS dataset. I just needed a nice way to display it. Since SAS updated this community message board, we aren't able to insert nice tables anymore. If I just copy my table and paste it here, it typically is a big text mess. But it seemed to come out somewhat nice here. This is what I want my output table to look like:

ptpt	subjid	visitnum	egnum
	101-001-001	10001	.
PREDOSE	101-001-001	30001	0.1
PREDOSE	101-001-001	30001	0.2
PREDOSE	101-001-001	30001	0.3
4HR_POSTDOSE	101-001-001	30001	4
PREDOSE	101-001-001	30015	0
4HR_POSTDOSE	101-001-001	30015	4
	101-001-001	30029	.
	101-001-001	30043	.
	101-001-001	70001	.
	101-003-003	10001	.
	101-003-003	20001	.
PREDOSE	101-003-003	30001	0.1
PREDOSE	101-003-003	30001	0.2
PREDOSE	101-003-003	30001	0.3
4HR_POSTDOSE	101-003-003	30001	4
PREDOSE	101-003-003	30015	0
4HR_POSTDOSE	101-003-003	30015	4
	101-003-003	30029	.
	101-003-003	30043	.
	101-003-003	70001	.
	101-003-004	10001	.
PREDOSE	101-003-004	30001	0.1
PREDOSE	101-003-004	30001	0.2
PREDOSE	101-003-004	30001	0.3
4HR_POSTDOSE	101-003-004	30001	4
PREDOSE	101-003-004	30015	0
4HR_POSTDOSE	101-003-004	30015	4
	101-003-004	30029	.
	101-003-004	30043	.
	101-003-004	70001	.

Re: Macro Outputting Extra Rows

djbateman — Fri, 20 Oct 2017 20:08:12 GMT

I gave the SELECT statement a try, and I ended up with fewer records than expected. The first timepoint for each visit was created (i.e, VISITNUM=30001 & EGNUM=0.1 and VISITNUM=30015 & EGNUM=0). None of the other timepoints were created.

Re: Macro Outputting Extra Rows

djbateman — Fri, 20 Oct 2017 20:21:03 GMT

This is an intense macro, but this is what I was able to do to get it to work. If anyone comes up with anything that is more simple, please feel free to pass it along!

data timepoints;
	length visitnum 8 ptpt $12 egnum 8;
	input visitnum ptpt $ egnum;
	cards;
30001	PREDOSE			0.1
30001	PREDOSE			0.2
30001	PREDOSE			0.3
30001	4HR_POSTDOSE	4.0
30015	PREDOSE			0.0
30015	4HR_POSTDOSE	4.0
;
run;
data subj;
	length subjid $11 visitnum 8;
	input subjid $ visitnum;
	cards;
101-001-001	10001
101-001-001	30001
101-001-001	30015
101-001-001	30029
101-001-001	30043
101-001-001	70001
101-003-003	10001
101-003-003	20001
101-003-003	30001
101-003-003	30015
101-003-003	30029
101-003-003	30043
101-003-003	70001
101-003-004	10001
101-003-004	30001
101-003-004	30015
101-003-004	30029
101-003-004	30043
101-003-004	70001
;
run;

%macro expand();
	proc sql noprint;
		select compress(put(count(distinct visitnum),best.)) into :nvisits from timepoints;
		select distinct visitnum into :visit1-:visit&nvisits. from timepoints;
		%do i=1 %to &nvisits.;
			select compress(put(count(*),best.)) into :ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
			select ptpt into :ptpt_&&visit&i.._1-:ptpt_&&visit&i.._&&&&ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
			select egnum into :egnum_&&visit&i.._1-:egnum_&&visit&i.._&&&&ntmpts_&&visit&i.. from timepoints where visitnum=&&visit&i.;
		%end;
	quit;

	data subj_expand;
		length ptpt $80;
		set subj;
		if visitnum=&visit1. then do;
			%do j=1 %to &&ntmpts_&visit1.;
				ptpt="&&ptpt_&visit1._&j.";
				egnum=&&egnum_&visit1._&j.;
				output;
			%end;
		end;
		%do i=2 %to &nvisits.;
			else if visitnum=&&visit&i. then do;
				%do j=1 %to &&&&ntmpts_&&visit&i.;
					ptpt="&&&&ptpt_&&visit&i.._&j.";
					egnum=&&&&egnum_&&visit&i.._&j.;
					output;
				%end;
			end;
		%end;
			else output;
	run;
%mend expand;
%expand;