Macro Outputting Extra Rows

Accepted Solution Solved
Reply
Regular Contributor
Posts: 242
Accepted Solution

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: 242

Re: Macro Outputting Extra Rows

Posted in reply to djbateman

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;

View solution in original post


All Replies
Super User
Posts: 21,572

Re: Macro Outputting Extra Rows

Posted in reply to djbateman

What do you want as output?

Regular Contributor
Posts: 242

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: 12,148

Re: Macro Outputting Extra Rows

Posted in reply to djbateman

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: 242

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:

 

ptptsubjidvisitnumegnum
 101-001-00110001.
PREDOSE101-001-001300010.1
PREDOSE101-001-001300010.2
PREDOSE101-001-001300010.3
4HR_POSTDOSE101-001-001300014
PREDOSE101-001-001300150
4HR_POSTDOSE101-001-001300154
 101-001-00130029.
 101-001-00130043.
 101-001-00170001.
 101-003-00310001.
 101-003-00320001.
PREDOSE101-003-003300010.1
PREDOSE101-003-003300010.2
PREDOSE101-003-003300010.3
4HR_POSTDOSE101-003-003300014
PREDOSE101-003-003300150
4HR_POSTDOSE101-003-003300154
 101-003-00330029.
 101-003-00330043.
 101-003-00370001.
 101-003-00410001.
PREDOSE101-003-004300010.1
PREDOSE101-003-004300010.2
PREDOSE101-003-004300010.3
4HR_POSTDOSE101-003-004300014
PREDOSE101-003-004300150
4HR_POSTDOSE101-003-004300154
 101-003-00430029.
 101-003-00430043.
 101-003-00470001.
Super User
Posts: 12,148

Re: Macro Outputting Extra Rows

Posted in reply to djbateman

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: 242

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: 242

Re: Macro Outputting Extra Rows

Posted in reply to djbateman

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 103 views
  • 0 likes
  • 3 in conversation