topic Re: add multiple record in SAS Programming

add multiple record

Srigyan — Thu, 21 Mar 2019 12:57:57 GMT

I am creating a table and updating the record with some queries.

%let final_File=F_OUTPUT.DS_daily_demand_F;
%let level= (catx('',OPTION_NUMBER,date,fascia));

data &final_File._checkpoints;
format Checkpoints $30. Total_Count 10. comment $100. Check_Date date9.;
run;

proc sql;

update &final_File._checkpoints as a
set
Checkpoints="Total Records",
Total_Count=(select Count(*) from &final_File.),
comment="This is total number cases here",
Check_Date=today();

proc append &final_File._checkpoints=
Checkpoints="Unique record",
Total_Count=(select Count(distinct product_ID) from &final_File.),
comment="This is unique number of cases",
Check_Date=today();

quit;

highlighted portion is not working, how should I write this code.

Re: add multiple record

noling — Thu, 21 Mar 2019 13:17:39 GMT

It looks like you're trying to alter your &final_File._checkpoints twice - once in your PROC SQL update, and maybe again in your proc append?

If you're updating records use PROC SQL. If you want to stack/add additional records use PROC APPEND with base=<original data set> data=<dataset with new records>. PROC APPEND can't use data step logic like you're trying to do. You can first create a temporary dataset with your new data before appending. See my example here.

You should also look at the PROC APPEND syntax, since it looks like you're completely guessing at it, which usually isn't a great approach for SAS.

Re: add multiple record

ballardw — Thu, 21 Mar 2019 14:19:41 GMT

@Srigyan wrote:

I am creating a table and updating the record with some queries.

%let final_File=F_OUTPUT.DS_daily_demand_F;
%let level= (catx('',OPTION_NUMBER,date,fascia));

data &final_File._checkpoints;
format Checkpoints $30. Total_Count 10. comment $100. Check_Date date9.;
run;

proc sql;

update &final_File._checkpoints as a
set
Checkpoints="Total Records",
Total_Count=(select Count(*) from &final_File.),
comment="This is total number cases here",
Check_Date=today();

proc append &final_File._checkpoints=
Checkpoints="Unique record",
Total_Count=(select Count(distinct product_ID) from &final_File.),
comment="This is unique number of cases",
Check_Date=today();

quit;

highlighted portion is not working, how should I write this code.

I suggest copying the Log output for the code that is not working. Copy from the log, the code and all messages related to the code an paste into a code box opened using the forum's {I} icon to preserve formatting.

I am fairly sure you are getting a number of error messages that are actually fairly clear.

Also describe what you actually attempting to do with the append code.

Re: add multiple record

Srigyan — Thu, 21 Mar 2019 14:44:16 GMT

I have done this in following way...

proc sql;

update &final_File._checkpoints as a
set
Checkpoints="Total Records",
Total_Count=(select Count(*) from &final_File.),
comment="This is total number cases here",
Update_Date=today();

insert into &final_File._checkpoints
set Checkpoints="Unique record",
Total_Count=(select Count(distinct &level.) from &final_File.),
comment="This is unique number of cases",
Update_Date=today();

quit;