Conditionally Proc append or Insert

[David_Billa]

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

[Kurt_Bremser]

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.

[David_Billa]

@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

[Kurt_Bremser]

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;

[David_Billa]

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;

[David_Billa]

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

[Kurt_Bremser]

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

[David_Billa]

@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.

[PaigeMiller]

---

@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.

[Tom]

---

@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

[Kurt_Bremser]

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

[David_Billa]

@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

[Tom]

You need a WHERE clause on the UPDATE statement.

update TABLE set VARIABLE=EXPRESSION where CONDITION;

[David_Billa]

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

[Tom]

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.