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) ;
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:
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) ;
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)
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?
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 )
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.