Ok, I first what I want the program to do / actions: 1. Fill in by hand the "Number" at the top of the code. This is an article number, varying from 000000001 until 9999999999. 2. As the database I am trying to look up this number only works with nine digits, I may need leading zero's. So I first put the articlenumber which I put in by hand into a variable. With the first dataset I convert the variable to a dataset (named NotNine). 3. Now I have a dataset, I need to convert the content to 9 characters, so I read the non-nine-char into a new dataset and via a put option I am converting it to a 9 character output / dataset. (named IsNine). 4. Next I need to get the content of the IsNine dataset (there is only one content in this case) and this has to be used in the SQL statement below. 5. The SQL statement should do a lookup, based on the articlenumber with nine positions. Steps 1 to 3 are working, but how do I get the content of de last dataset (not the dataset) to be usable in the SQL code in the next steps? %let Number = 12345678;
/*grab the variable 'Number' from the previous step and put the value in "NewNumber"*/
Data NotNine;
NewNumber = &Number;
run;
/*Create from "NewNumber " a nine char number and put this in "NineNumber "*/
DATA IsNine (DROP=NewNumber);
SET NotNine;
NineNumber = (PUT(NewNumber, z9.));
RUN; PROC SQL; /*below code is now not working, as &NineNumber is not found, but I want the last line to be in this example "select articlenumber IN (012345678)) ;"*/ CONNECT TO ODBC as con1 (datasrc="dbsArticles" USER="fakeuser" PASS="abcd1234"); CREATE TABLE TEMP AS select * FROM CONNECTION TO con1( select articlenumber IN (&NineNumber)) ; I hope this clarifies more 🙂 Probably I don't need the first NotNine dataset, but I used this to have a trace what happened in my code.
... View more