BookmarkSubscribeRSS Feed
never
Calcite | Level 5

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

5 REPLIES 5
AncaTilea
Pyrite | Level 9

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:

never
Calcite | Level 5

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

art297
Opal | Level 21

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)

never
Calcite | Level 5


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?

art297
Opal | Level 21

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 )

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3419 views
  • 6 likes
  • 3 in conversation