Read a SAS file determine if record present and then perform insert or update to same file

[JohnO1]

My issue is I want to read a file for a record using a unique value. If found I want to modify the record if  not found I want to insert a new record. I can do this in any number of other programming  languages I have worked with (COBOL, PL\I, VB Script, etc....)  but I am having a complete mental blockage for doing this in SAS. 

I figure I will need to do it in a macro inside a Data _NULL_ step. with something like

%Macro InsertUpdate; 

   data _null_;

    set inputfle;

    where account = 12345;

    %if found - do modify logic;

    %else - do insert logic;

%mend InsertUpdate;

Is there a automatic variable I can look at to determine if a record was returned and do I need to perform this logic in a macro?

[Reeza]

If you have X command, you can also consider doing it in Powershell or Unix and call the script or pass the commands from SAS.

[JohnO1]

Reeza - Not sure what X command is. I am not on Unix and I have never worked with powershell so at this point need to do it all in SAS. I have the insert and update logic modules written (not sure if they work yet) the oly thing I am missing is how to determine if the record I looking for was returned from the Set and where combination

[Reeza]

If you have a SET and WHERE the dataset created will be zero if the record is not found. In that case you're looking for logic similar to this example which prints a message to the log if the dataset is 0.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#p011imau3tm4jen1u...

data one;
x=1;
run;
data two;
stop;
run;

%macro drive(dsn);
%let dsid=%sysfunc(open(&dsn));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    proc print data=&dsn;
     title "This is data from data set &dsn"; 
    run;
   %end;
   %else %do;
    data _null_;
     title;
     file print;
     put _page_;
     put "Data set &dsn is empty.";
    run;
   %end;
 %end;
 %else %put &dsn cannot be open.;
   
%mend drive;
%drive(one)
%drive(two)

[RW9]

Think your overcomplicating it, and you haven't shown any examples so to guess:

data _null_;
  infile "abc.txt";
  file "abc.txt";
  input;
  if index(_infile_,"your string") > 0 then put "string you want";
  else do;
    put _infile_;
    put "new record";
  end;
run;

[Astounding · Accepted solution]

You're in the middle of a DATA _NULL_ step, so you will need a new SAS step to perform any modifications or updates.  Here's an approach:

%Macro InsertUpdate; 

   %local found;

    %let found=N;

   data _null_;

    set inputfle;

    where account = 12345;

    call symput('found', 'Y');

    stop;

   run;

   %if &found=Y %then %do;

      *** Whatever it takes to update;

   %end;

   %else %do;

      *** Whatever it takes to insert;

   %end;

%mend InsertUpdate;

%InsertUpdate

If no observations meet the WHERE condition, the DATA step ends before executing CALL SYMPUT.  So &FOUND remains with a value of N.  If an observation is found that meets the WHERE condition, CALL SYMPUT changes &FOUND to Y, and STOP halts the data step (no need to locate any additional observations meeting the WHERE condition).

How you insert/update is up to you.

[JohnO1]

Thanks Based on what I am reading in your post this will work for what I need to do.

[Tom]

If you explain what you are really trying to do then perhaps you don't need any conditional logic at all.

Try to think in terms on applying actions to a full dataset rather than to individual records. More like the set operations of SQL than the step-by-step logic requried by lower level languages lke PL/I.

So it might be what you really want to do is just merge your old data with the new data to make and totally new dataset. Or perhaps you want to apply transactions using the UPDATE or MODIFY statements in a data step.