topic Re: Conditionally Proc append in SAS Programming

Conditionally Proc append or Insert

David_Billa — Sun, 19 Apr 2020 09:06:01 GMT

Assume I have two datasets. One dataset (details) has five variables namely Run_Id,Company_code,Datasource,Table_Name,Table_Count.

Out of these 5 variables, first three variables get data from one program which we no need to worry. Now I've feed the data into last two variables Table_Name and Table_Count based on Run_ID by one program.

I've a program as below which will create three variables - Run_ID,Table_Name,Table_Count

Below program is creating a table called 'Insurance', but I'm not sure how to append/insert data into IFR.Details when Run_ID matches between the datasets Insurance and IFR.Details.

Run_ID in Insurance dataset is extract from other WORK dataset Status_tech.

Proc append below is not working correctly and I'm looking some help here.

At beginning IFR.Details dataset will be like,

Run_ID	Company_code	Datasource	Table_name	Table_count
12345	ABC	Database
12346	DEF	Excel

Insurance dataset will be like,

Run_ID	Table_name	Table_count
12345	Insurance	87

Then I need IFR.Details dataset which should looks like as below. I just have to feed the data to two variables from Insurance dataset when Run_ID matcjes between Insurance and IFR.Details dataset.

Run_ID	Company_code	Datasource	Table_name	Table_count
12345	ABC	Database	Insurance	87
12346	DEF	Excel

options symbolgen mlogic mprint;

%MACRO STATUS_TECH_UPDATE(tab);
	%put &tab;
	proc sql;
		create table &tab as
			select 
				"&tab" as table_name
				,count(1) as table_count
				,B.run_id
			from IFR.&tab A, STATUS_TECH B
				where A.RUN_ID = B.RUN_ID;
	quit;

/*append/insert data into IFR.Details when Run_ID matches between the datasets Insuarnce and IFR.Details.*/
/*below append is not working as excepted and I'm not sure how to append based on Run_ID*/
	proc append base=IFR.Details data=&tab(drop=run_id) force;
	run;
%MEND;

%STATUS_TECH_UPDATE(Insurance);

Re: Conditionally Proc append

Kurt_Bremser — Sun, 19 Apr 2020 08:56:29 GMT

Remove all macro elements from your code, and run it with literally set datset names.

Run the code in steps, read the log, and inspect the resulting dataset(s) after each individual step.

Do not start to wrap your code into a macro before it is verified that it works.

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 09:06:42 GMT

@Kurt_Bremser

At beginning IFR.Details dataset will be like,

Run_ID	Company_code	Datasource	Table_name	Table_count
12345	ABC	Database
12346	DEF	Excel

Insurance dataset will be like,

Run_ID	Table_name	Table_count
12345	Insurance	87

Then I need IFR.Details dataset which should looks like as below. I just have to feed the data to two variables from Insurance dataset when Run_ID matcjes between Insurance and IFR.Details dataset.

Run_ID	Company_code	Datasource	Table_name	Table_count
12345	ABC	Database	Insurance	87
12346	DEF	Excel

Re: Conditionally Proc append

Kurt_Bremser — Sun, 19 Apr 2020 10:53:13 GMT

What you want is a simple update, not an append:

data details;
infile datalines truncover;
input Run_ID :$5. Company_code :$3. Datasource :$10. Table_name :$10. table_count;
datalines;
12345 ABC Database	 	 
12346 DEF Excel
;

data insurance;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insurance 87
;

data want;
update
  details
  insurance
;
by run_id;
run;

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 12:04:27 GMT

I;m getting an error when I tried with the code below

data IFR.details;

update IFR.details &tab;

by run_id;

run;

21        + data IFR.details    update IFR.details INSURANCE;    by run_id;    run;

ERROR:  This table will not be replaced. This engine does not support the REPLACE option.

When I tried with code as below, I'm not seeing any values in table_name and table_count. I used this code inside the macro as I mentioned in the Initial post. Am I missing something or misplacement of proc SQL update?

proc sql;

update IFR.details as a

set table_name=(select table_name from &tab as b where a.run_id=b.run_id),

table_count=(select table_count from &tab as b where a.run_id=b.run_id);

quit;

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 12:07:56 GMT

@Kurt_Bremser

Used proc SQL update as mentioned below. Run_id is matching between the tables but still I'm not seeing any values under Table_name and Table_count fields.

options symbolgen mlogic mprint;

%MACRO STATUS_TECH_UPDATE(tab);
	%put &tab;
	proc sql;
		create table &tab as
			select 
				"&tab" as table_name
				,count(1) as table_count
				,B.run_id
			from IFR.&tab A, STATUS_TECH B
				where A.RUN_ID = B.RUN_ID;
	quit;

   proc sql;
   update IFR.details as a
   set table_name=(select table_name from &tab as b where a.run_id=b.run_id),
   table_count=(select table_count from &tab as b where a.run_id=b.run_id);                     
   quit;

%MEND;

%STATUS_TECH_UPDATE(Insurance);

Re: Conditionally Proc append

Kurt_Bremser — Sun, 19 Apr 2020 12:12:29 GMT

Please show details of the IFR library. A SAS library allows this kind of update.

Re: Conditionally Proc append

Kurt_Bremser — Sun, 19 Apr 2020 12:14:54 GMT

You're still using your macro. Make the code work WITHOUT(!!!!) ANY macro elements, before starting to make it dynamic.

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 12:15:36 GMT

@Kurt_Bremser

Per data it should update only one row in target table but log says 23 rows were updated.

Log says

17        +            proc sql;    update IFR.details as a    set table_name=(select table_name from 
      INSURANCE as b where a.run_id=b.run_id),    table_count=(select table_count from 
      INSURANCE as b where

18        + a.run_id=b.run_id);
NOTE: 23 rows were updated in IFR.details

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 13:19:10 GMT

@Kurt_Bremser

If I place the proc sql update outside the macro it is working but it is
not working if I wrap it inside the macro.

I want to update the target table using a macro as the &tab macro variable
resolves to multiple table names which should run for each table one by one.

Only proc sql update step which was inside the macro is not working, other
steps are running fine.

Re: Conditionally Proc append

PaigeMiller — Sun, 19 Apr 2020 13:22:13 GMT

@David_Billa wrote:

@Kurt_Bremser

If I place the proc sql update outside the macro it is working but it is
not working if I wrap it inside the macro.

Show us this working non-macro code.

Re: Conditionally Proc append

Tom — Sun, 19 Apr 2020 13:32:00 GMT

@PaigeMiller wrote:

@David_Billa wrote:

@Kurt_Bremser

If I place the proc sql update outside the macro it is working but it is
not working if I wrap it inside the macro.

Show us this working non-macro code.

Maybe this time? https://en.wiktionary.org/wiki/third_time%27s_a_charm

Re: Conditionally Proc append

Tom — Sun, 19 Apr 2020 13:34:25 GMT

You need a WHERE clause on the UPDATE statement.

update TABLE set VARIABLE=EXPRESSION where CONDITION;

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 13:39:22 GMT

@Tom @Kurt_Bremser @PaigeMiller

Below code works fine outside the macro but not within the macro. Please see the comment before second step.

I did added WHERE before update Statement as @Tom mentioned but still no luck if I run it within the macro

proc sql;
select "'"||compress(RUN_ID)||"'" into :mac separated by ","
from ifr.details;
quit;

/*If I run with one table called INSURANCE it's working*/
/*But in real life I will be creating macro variable called 'tab' which will holds the values of table names and I want to to run the proc sql update for each table*/
proc sql;
update ifr.details (where = (RUN_ID IN (&mac.)))  as a
set table_name=(select table_name from INSURANCE as b where a.run_id=b.run_id),
table_count=(select table_count from INSURANCE as b where a.run_id=b.run_id);                     
quit;

Re: Conditionally Proc append

Tom — Sun, 19 Apr 2020 13:44:52 GMT

That code is updating ALL table name values. Show an example of the code that runs for only ONE table name. Just pick one of the table name values and put it in as a quoted string into the code.

When you get it working then replace the literal name with a macro variable reference.

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 13:51:36 GMT

Could you please help me understand how to put the table name in quoted
string?

In my code from previous post, INSURANCE is one of the table name value.

Re: Conditionally Proc append

Tom — Sun, 19 Apr 2020 14:19:47 GMT

Don't you want to update the values of RUN_ID that are in INSURANCE? Not all of the values or RUN_ID.

Also don't remove leading or embedded spaces from RUN_ID, that could lead to mismatches.

Not sure the impact of trying to use a WHERE= clause on the target of an UPDATE statement. What would that even mean?

So try code like this:

proc sql;
select quote(trim(RUN_ID)) into :mac separated by ' '
  from INSURANCE
;
quit;

proc sql;
update ifr.details a
  set table_name=(select table_name from INSURANCE b where a.run_id=b.run_id)
    , table_count=(select table_count from INSURANCE b where a.run_id=b.run_id)
  where a.run_id in (&mac)
;
quit;

You can eliminate the macro variable.

proc sql;
update ifr.details a
  set table_name=(select table_name from INSURANCE b where a.run_id=b.run_id)
    , table_count=(select table_count from INSURANCE b where a.run_id=b.run_id)
  where a.run_id in (select b.run_id from INSURANCE b)
;
quit;

Now you can try replacing INSURANCE with a macro variable.

%let tab=INSURANCE;
proc sql;
update ifr.details a
  set table_name=(select table_name from &tab b where a.run_id=b.run_id)
    , table_count=(select table_count from &tab b where a.run_id=b.run_id)
  where a.run_id in (select b.run_id from &tab b)
;
quit;

If that still works then try wrapping it in a macro.

Re: Conditionally Proc append

Kurt_Bremser — Sun, 19 Apr 2020 14:35:12 GMT

Corrected SQL, updates only one observation with my previous example data:

proc sql;
update details a
  set
    table_name = (select table_name from insurance b where a.run_id = b.run_id),
    table_count = (select table_count from insurance b where a.run_id = b.run_id)
  where a.run_id in (select run_id from insurance)
;
quit;

To put that into a macro, so you can update from a series of tables:

%macro update(table);
proc sql;
update details a
  set
    table_name = (select table_name from &table. b where a.run_id = b.run_id),
    table_count = (select table_count from &table. b where a.run_id = b.run_id)
  where a.run_id in (select run_id from &table.)
;
quit;
%mend;

Now call that for every table:

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
insurance
;

Complete code with data, tested on SAS UE:

data details;
infile datalines truncover;
input Run_ID :$5. Company_code :$3. Datasource :$10. Table_name :$10. table_count;
datalines;
12345 ABC Database	 	 
12346 DEF Excel
;

data insurance;
input Run_ID :$5. Table_name :$10. Table_count;
datalines;
12345 Insurance 87
;

%macro update(table);
proc sql;
update details a
  set
    table_name = (select table_name from &table. b where a.run_id = b.run_id),
    table_count = (select table_count from &table. b where a.run_id = b.run_id)
  where a.run_id in (select run_id from &table.)
;
quit;
%mend;

data _null_;
input tablename $32.;
call execute(cats('%nrstr(%update(',tablename,'))'));
datalines;
insurance
;

Re: Conditionally Proc append

David_Billa — Sun, 19 Apr 2020 14:51:36 GMT

Thanks a ton!
Can we also dynamically delete the WORK datasets which will be created
while the macro executes?

Re: Conditionally Proc append

Tom — Sun, 19 Apr 2020 14:56:19 GMT

You can use PROC DELETE. Or in SQL you can use DROP TABLE statement.