Help using Base SAS procedures

proc sql insert in macro loop

Reply
Occasional Contributor
Posts: 5

proc sql insert in macro loop

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, 8) ;

%loop(file3, 13) ;

%loop(file4, 20) ;

Super Contributor
Posts: 543

Re: proc sql insert in macro loop

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:

Occasional Contributor
Posts: 5

Re: proc sql insert in macro loop

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

PROC Star
Posts: 7,363

Re: proc sql insert in macro loop

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, 8)

%loop(file3, 13)

%loop(file4, 20)

Occasional Contributor
Posts: 5

Re: proc sql insert in macro loop


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?

PROC Star
Posts: 7,363

Re: proc sql insert in macro loop

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 )

Ask a Question
Discussion stats
  • 5 replies
  • 1351 views
  • 6 likes
  • 3 in conversation