How to pass values from a dataset to PROC SQL

[deleted_user]

Hi all

I am running SAS under OS/390 and I have a small problem.

What I need to do is simply grab a list of part numbers from an input dataset and pass this list of part numbers to a SQL statement to update the Price column of a table. The code looks like this:

OPTIONS S = 72 DB2SSID = DB39 ERRORABEND ;
DATA C;
INFILE DATOUT;
INPUT @01 OPART $12.;
CALL SYMPUT ('NPART',TRIM(LEFT(PUT(OPART,CHAR12.))));

PROC SQL;
LIBNAME AAUAIBT DB2 SSID=DB39;
EXECUTE ( UPDATE table.test
SET COST=1111.11
WHERE PART_NUMBER=&NPART) BY DB2;
QUIT;
RUN;

The problem I am having is that the CALL SYMPUT goes straight to the last entry in the input dataset and retrieves the last value - it does not pass any of the previous part numbers across to the SQL statement, expect for the last one.

How can I solve this ?

Thanks for your help.

[Cynthia_sas]

Hi:
This is not an ODS or Reporting procedure question. Your best bet for help is to contact SAS Tech Support.

What's happening is that through every loop of your Data Step program, you ARE creating a macro variable and then the next loop of your DATA step program is overwriting the previous record's value of the macro variable with the next record's value. ALL of the DATA step program will execute before ANY of the PROC SQL executes. So only the last value is in the Global Symbol Table by the time your PROC SQL starts executing.

Tech Support can help you figure out whether this is the best approach for what you want to do and if you are going to use macro variables, they can help you correct the macro code the you need to make this work.

cynthia

[deleted_user]

Hi Cynthia

Thanks for taking the time to reply to my problem.

I eventually managed to resolve this issue.

I used the PROC SQL with the LIBNAME (implicit Pass Through)

Thanks!
Shelton.