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

Posted in reply to AncaTilea

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,467

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,467

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
  • 1534 views
  • 6 likes
  • 3 in conversation