How to get a single (first) value from a dataset into a variable.

[joostman1]

Hi, Im fairly new to SAS and am wresting with my code which, in my head, seems easy to do. Unfortunately SAS and I are not really understanding each other 😉 

My purpose is to provide a random nummer from 1 to 9 characters and convert this into a 9 character output, possible with leading zeros.
For Example if I enter 12345 I wanted the data to be like 000012345 and get this value into a variable for new actions.

I am able to get a dataset from this

%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;

Now I have two datasets, "NotNine" and "IsNine"

In my example above:

- "NotNine" contains "NewNumber = 12345"

- "IsNine" contains "NineNumber = 000012345"

Now I need to have the content of NineNumber for an SQL statement. Thus I was thinking I need to get the NineNumber into a variable (%LED) statement.

Putting things like 

/*Create from "NewNumber " a nine char number and put this in "NineNumber "*/
DATA IsNine (DROP=NewNumber);
SET NotNine;
%LET NineNumber = (PUT(NewNumber, z9.));
RUN;

did not work.
Also creative variations like was not what worked.

/*Create from "NewNumber " a nine char number and put this in "NineNumber "*/
DATA IsNine (DROP=NewNumber);
SET NotNine;
NineNumber = (PUT(NewNumber, z9.));
%LET NewNineNumber = NineNumber;
RUN;

I am guessing this is because I (have and) only want the contents of the first entry of the dataset, but not specifying this somewhere?

But how to I get the first entry of the dataset to be put in a variable that I can use in a next PROC SQL statement?

[PaigeMiller]

Not clear why you need a macro variable, nor is it clear why you need so much code.

data isnine;
    number=1234567;
    ninenumber=put(number,z9.);
run;

Now I need to have the content of NineNumber for an SQL statement.

But you never show an SQL command that uses the value of ninenumber.

[ballardw]

Your subject like is "get a single (first) value from a dataset into variable". You don't show an existing data set to get a value from, you show a macro variable. It is very easy to get the values of the first observations from a data set:

data temp;
   set somedatasetname (obs=1);
run;

Pulls only the first observation from a data set into the temp data set.

If you mean a first non-missing value for a specific variable then you need to tell us exactly what you expect to do with it.

And if the purpose is a truly "random number" , not one you select, then use one of the SAS Random number generating functions.

data example;
   x=rand('integer',100000000);
   xchar = put(x,z9.);
run;

which selects a number between 1 and 100000000 randomly then creates a character version with leading 0s.

Some comments: to assign a value to a macro variable in the macro language the syntax is %LET

To assign the value of a data step variable in a data step the syntax is Call symput or Call symputx('variablename', datasetvalue)

data _null_;
   x= 4567;
   call symputx('macrovarname',put(x,z6.))
run;
%put macrovarname= &macrovarname.;

Third, macro statements like %let that appear in the body of data step code are compiled before the data step executes and never see the data set variable values.

It is almost certain that you do not need any data step if you want to stick a single macro variable value into Proc SQL. But since you haven't shown how you intend to use such value we can't make a specific suggestion.

[joostman1]

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. 

[PaigeMiller]

First step: If you are typing in the number yourself, you don't need it to be in a macro variable, you can also type it into a data step. I simplify your method to one data step, which contains the original number and then converts to a character string with leading zeros and turns this into a macro variable. I have written code for this. (Your method has overcomplicated things, in my opinion)

data _null_;
    number=1234567;
    call symputx('ninenumber',put(number,z9.));
run;

Now you have a macro variable named &ninenumber that contains the desired value.

Second step is to run the SQL and find the desired record with &ninenumber. I can't write code for the second step that will work because I don't know your database, but macro variable &ninenumber should have the desired value if you run the above code and can be used in your SQL.

[Kurt_Bremser · Accepted solution]

DATA IsNine (DROP=NewNumber);
SET NotNine;
NineNumber = (PUT(NewNumber, z9.));
%LET NewNineNumber = NineNumber;
RUN;

Macro statements are resolved by the macro processor while the code is being fetched for compilation and execution, so long before the data step runs. Your code is equivalent to this:

%LET NewNineNumber = NineNumber;
DATA IsNine (DROP=NewNumber);
SET NotNine;
NineNumber = (PUT(NewNumber, z9.));
RUN;

so the macro variable &NewNineNumber will contain the text "NineNumber" (without the quotes).

To save data step values to a macro variable, use CALL SYMPUT or CALL SYMPUTX (the latter is preferred, as it does number to character conversion without a NOTE, strips the character value of leading and trailing blanks, and allows you to select the macro variable scope through a third parameter).

Your data step should look like this:

data IsNine (drop=NewNumber);
set NotNine (obs=1);
call symputx('NewNineNumber',put(NewNumber, z9.),'g');
run;

[joostman1]

Thank you! That was exacly working and I was looking for!
Nice to have a new knowledge how to use data from a dataset as input for a new action!

[joostman1]

I was surprised, looking at the datasets after the execution that dataset IsNine does not seem to have any content.
I was thinking this would contain the header "NewNineNumber" and the value in 9 characters.

What I seem to see is an empy dataset, no header and 1 or actually no observation.