topic Re: proc sql insert in macro loop in SAS Procedures

proc sql insert in macro loop

never — Thu, 10 Jan 2013 16:10:48 GMT

My issue is as follows…

In the following script I run a macro with a loop over several files. I intended to insert and therefore append new rows into one previously created table (libname.table) from multiple other files. However, when I run this macro, after each macro instance execution the table is completely replaced by the new data rather than appending to the previous data.

Any ideas what is happening and/or how to fix? I've made a proc transpose fix that will do the job, but I'd really like to know why this isn't working as anticipated.

Thanks!

%macro loop(file, count) ;

%do i = 1 %to &count ;

proc sql ;

insert into libname.table

select id as personid,

date as date,

diagnosis&i as diagnosis

from &file

where diagnosis&i <> ‘’ ;

quit ;

%end ;

%mend loop ;

%loop(file1, 10) ;

%loop(file2, 😎 ;

%loop(file3, 13) ;

%loop(file4, 20) ;

Re: proc sql insert in macro loop

AncaTilea — Thu, 10 Jan 2013 16:40:50 GMT

So, I feel like I got it to work.

*Create a temp data that I will use to insert;

data t1;

do i = 1 to 10;

diagnosis1 = i;

diagnosis2 = i-2;

diagnosis3=5;

output;

end;

run;

*the data I want to insert into;

data t2;

do i = 1 to 20;

diagnosis = i;

output;

end;

run;

options mprint mlogic symbolgen;

%macro loop(file, count) ;

%do i = 1 %to &count. ;

proc sql ;

insert into work.t2

select i as personid, diagnosis&i. as diagnosis

from &file

where diagnosis&i. <> . ;

quit ;

%end ;

%mend loop ;

%loop(t1, 3) ;

So, thy this and see if it works.

I feel like I use exactly the same code.;

:smileyconfused:

Re: proc sql insert in macro loop

never — Thu, 10 Jan 2013 17:04:17 GMT

Does it work if you run the macro over two data sets rather than one?

Extending your code, for example:

*Create a temp data that I will use to insert;

data t1;

do i = 1 to 10;

diagnosis1 = i;

diagnosis2 = i-2;

diagnosis3=5;

output;

end;

run;

*Create a second temp data that I will use to insert;

data t3;

do i = 1 to 10;

diagnosis1 = i+6;

diagnosis2 = i-12;

diagnosis3= .;

diagnosis4 = i*i ;

output;

end;

run;

*the data I want to insert into;

data t2;

do i = 1 to 20;

diagnosis = i;

output;

end;

run;

options mprint mlogic symbolgen;

%macro loop(file, count) ;

%do i = 1 %to &count. ;

proc sql ;

insert into work.t2

select i as personid, diagnosis&i. as diagnosis

from &file

where diagnosis&i. <> . ;

quit ;

%end ;

%mend loop ;

%loop(t1, 3) ;

%loop(t3, 4) ;

Re: proc sql insert in macro loop

art297 — Thu, 10 Jan 2013 17:39:40 GMT

A minor change to your proc sql call worked for me. I was receiving odd results until I changed you proc sql call to explicitly identify the variables being inserted.

data table;

informat date date9.;

format date date9.;

input personid date diagnosis $;

cards;

1 10jun2012 1

2 11jun2012 2

;

data file1;

informat date date9.;

format date date9.;

input id date (diagnosis1-diagnosis10) ($);

cards;

3 12jun2012 1 . . . . . . . . .

4 13jun2012 1 2 3 . . . . . . .

;

data file2;

informat date date9.;

format date date9.;

input id date (diagnosis1-diagnosis8) ($);

cards;

5 14jun2012 1 . . . . . . .

6 15jun2012 1 2 3 . . . . .

;

data file3;

informat date date9.;

format date date9.;

input id date (diagnosis1-diagnosis13) ($);

cards;

7 16jun2012 2 5 . . . . . . . . . . .

8 17jun2012 3 2 1 . . . . . . . . . .

;

data file4;

informat date date9.;

format date date9.;

input id date (diagnosis1-diagnosis20) ($);

cards;

7 18jun2012 4 1 . . . . . . . . . . . . . . . . . .

8 19jun2012 5 6 7 . . . . . . . . . . . . . . . . .

;

%macro loop(file, count) ;

%do i = 1 %to &count ;

proc sql ;

insert into /*libname.*/table

(personid,date,diagnosis)

select id,date,diagnosis&i.

from &file

where diagnosis&i ne ''

;

quit ;

%end ;

%mend loop ;

%loop(file1, 10)

%loop(file2, 😎

%loop(file3, 13)

%loop(file4, 20)

Re: proc sql insert in macro loop

never — Thu, 10 Jan 2013 17:46:48 GMT

Shoot, I thought about that too but the results didn't change for me.

I'm using sas on UNIX, could that have anything to do with it?

Re: proc sql insert in macro loop

art297 — Thu, 10 Jan 2013 18:29:23 GMT

I don't have any UNIX experience, thus can't answer your question. However, according to the documentation:

Restriction:

You cannot use INSERT INTO on a table that is accessed with an engine that does not support UPDATE processing

If UNIX is using CEDA to process the file, then it definitely doesn't support update processing (see: SAS(R) 9.2 Language Reference: Concepts, Second Edition )