## Macro Outputting Extra Rows

Solved
Regular Contributor
Posts: 247

# Macro Outputting Extra Rows

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;``````

Accepted Solutions
Solution
‎10-26-2017 09:21 AM
Regular Contributor
Posts: 247

## Re: Macro Outputting Extra Rows

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;``````

All Replies
Super User
Posts: 23,343

## Re: Macro Outputting Extra Rows

What do you want as output?

Regular Contributor
Posts: 247

## Re: Macro Outputting Extra Rows

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.

Super User
Posts: 13,358

## Re: Macro Outputting Extra Rows

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-dat... 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.

Regular Contributor
Posts: 247

## Re: Macro Outputting Extra Rows

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 .
Super User
Posts: 13,358

## Re: Macro Outputting Extra Rows

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*/

Regular Contributor
Posts: 247

## Re: Macro Outputting Extra Rows

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.

Solution
‎10-26-2017 09:21 AM
Regular Contributor
Posts: 247

## Re: Macro Outputting Extra Rows

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;``````
☑ This topic is solved.