topic Re: Data Step: Check if data is in column of CSV file in New SAS User

Data Step: Check if data is in column of CSV file

sgnd — Tue, 07 Nov 2023 12:32:48 GMT

Hello,

I have got a SAS .egp that does a evaluation of data for different cases and then puts this data in one Excel-file with 6 data sheets.

Now I have to change the program: so that in the data step where the cases are checked, should the program check if the ID is not in a CSV file (one column with the IDs only).

How can I do this?

I have already this:

proc import datafile="temp.csv"
        out=temp
        dbms=csv
        replace;
    

     getnames=no;
run;

proc print data=work.temp;
run;

And now I want to check it with this code:

    data schritt2;
/* this is working correctly */
        set data_table; /* contains colums ARBEITSSTUNDENBEZAHLT, PERSONALNUMMER etc. */
        if ARBEITSSTUNDENBEZAHLT >400;

/* this is what I added: */
        %LET var=PERSONALNUMMER;
        
        proc sql;
		  select *
		  from WORK.TEMP 
		  where VAR1 = var; 
		
		  quit;
		  
/* how to do this? */
		if (ergebnis != TRUE); 

    run;

I hope someone could give me a hint.

Thanks

Sebastian

Re: Data Step: Check if data is in column of CSV file

SASJedi — Tue, 07 Nov 2023 13:06:41 GMT

Sebastian,
You can't have an SQL step in the middle of a DATA step, and from the code shown I have no idea what the %LET statement was intended to accomplish. Please submit a few rows of the data sets you HAVE, and a few rows of what the desired result would look like. Here is a link to an article that will help you share your existing data more easily:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712
All the best,
Mark

Re: Data Step: Check if data is in column of CSV file

Patrick — Tue, 07 Nov 2023 23:14:55 GMT

Assuming you've got a base table and a lookup table for valid id's that both contain a variable ID then below code should work with minimal change.

data check;
  if _n_=1 then
    do;
      /* create and populate in-memory hash lookup table with valid id's */
      dcl hash h1(dataset:'work.valid_id');
      h1.defineKey('id');
      h1.defineDone();
    end;
  set work.base_table;
  /* below condition becomes TRUE if ID exists in hash lookup table */
  if h1.check()=0;
run;