BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mariange8282
Obsidian | Level 7

Hi, I have a table X with column Y with just one observacion = 2

2

 

I need to have this 2 as a numeric value in a variable. 

 

Thanks, 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, where to start?  We cannot see any of the data you have, or what you have set certain macro variables and such like to.  We can only see what you have posted here.  So your block of code, I have updated this (note consistent casing, indentations, use of code block (its the {i} above post):

 

%let nombre=d_20500_Ward_6;
data x_new;
  set s3;
  a=put(col1,1.);
run;
data &nombre.;
  set cluster.&nombre. (where=(numcluster="&a."));
run;

Now without seeing anything else, this code looks syntactically correct - assuming a dataset s3 exists and contains a character field called col1.  This also assumes that in the library cluster there is a dataset called d_20500_ward_6 which contains a character variable called numcluster.  And it assumes there is a macro variable defined somewhere called a.  If all those assumptions are true then the code is fine.  However, I do not have a dataset called s3, a library called cluster nor a dataset called d_20500_ward_6 so I cannot say.  

Same with you second bit of code:

%let nombre=d_20500_Ward_6;
proc sql;
  /* this code doesn't do anything - just displays data in the output window */
  select  *    
  from    s3;

  create table s4 as 
  select  * 
  from    cluster.&nombre. 
  where   NUMCLUSTER="&a.";
quit;

Assuming you have a library called cluster and a dataset called d_20500_ward_6 in that libary which contains a character variable numcluster, then the code is good to go.  

 

What I am guessing is your problem - as you haven't posted any warning messages - is that the variable numcluster is actually numeric, in which case you need to change either of your codes to:

%let nombre=d_20500_Ward_6;
data x_new;
  set s3;
  a=put(col1,1.);
run;
data &nombre.;
  set CLUSTER.&nombre. (where=(numcluster=&a.));
run;


%let nombre=d_20500_Ward_6;
proc sql;
  /* this code doesn't do anything - just displays data in the output window */
  select  *    
  from    s3;

  create table s4 as 
  select  * 
  from    cluster.&nombre. 
  where   NUMCLUSTER=&a.;
quit;

 

View solution in original post

14 REPLIES 14
Kurt_Bremser
Super User

I guess that Y is of type character.

data x_new;
set x;
y_num = input(y,best.);
run;

will create the numeric variable y_new and assign it the value from y, if that can reasonably be converted.

RahulG
Barite | Level 11

I really do not understand your problem fully. It would be nice if you can give more description.

Otherwise try below code.

 

data X;
input Y;
datalines;
2
;
mariange8282
Obsidian | Level 7

I have a table X with just one column and one row. Just one value Z type Numeric. 

 

I need to know Z because I need to filter DATA2 according with Z

 

data DATA3;
set DATA2(where=(COL1=&Z));
run;

 

The problem is my Z can not be a column in a table, it have to be a single variable. Por example 

if I try with %let Z ='2';

I get data3 correctly, but I need the info in the table X. 

mariange8282
Obsidian | Level 7

The solution was:

 

PROC SQL;
select * from X as Z;
quit;

data DATA3;
set DATA2(where=(COL1=&Z));
run;

 

Thanks

Reeza
Super User

That code isn't correct. Or at least not cohesive for the portion shown. 

 

Reeza
Super User

That code isn't correct. Or at least not cohesive for the portion shown. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, that code makes no sense.  What is X, why are you selecting all observations from it, then in a separate datastep filtering out where col1= a macro variable - which is also not specified.  Just taking your code as basis:

data want;
  set x;
  where col1="&z.";
run;

Would be syntactically correct, and logical per what you have provided there, however that depends on what &z represents.

 

mariange8282
Obsidian | Level 7

Yes, you right.. I dont know why my code works yesterday.... 

 

Now, the error I have with yours is:

 

ERROR: WHERE clause operator requires compatible variables.

Reeza
Super User

I think what you're trying to do is this:

 

proc SQL;

create table data3 as

select * 

from data2

where col1 in 

    (Select y from x);

quit;  

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, that is down to the fact that you haven't told us what the data looks like.  This is why posting your test data - in the form of a datastep is so important, we can't see what you are working with.

 

Anyways, either the macro variable or the variable in question is a different format to the other, so change one or the other to match your data:

data want;
  set x;
  where col1=&z.;
run;

 

mariange8282
Obsidian | Level 7

I am sorry for not explain myself clear...

 

This is my issue:

 

Need to get the info in a cell, put in a variable and try to filter an other dataset according that info.
I've try two ways.. no success.
Frist one:
Comment: into of the cell is in the location COL1,1.
Show the error.

%let nombre=d_20500_Ward_6;
data x_new;
set s3;
a = put(COL1,1.);
data &nombre;
set CLUSTER.&nombre(where=(NUMCLUSTER='&a.'));
run;

Second one:
Comment: Same error

%let nombre=d_20500_Ward_6;
PROC SQL ;
select * from s3 as a;
CREATE TABLE s4 as select * from cluster.&nombre where NUMCLUSTER="&a.";

Pd: I've been try with NUMCLUSTER=&a, &'a.', &a .. no success.

Any ideas?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, where to start?  We cannot see any of the data you have, or what you have set certain macro variables and such like to.  We can only see what you have posted here.  So your block of code, I have updated this (note consistent casing, indentations, use of code block (its the {i} above post):

 

%let nombre=d_20500_Ward_6;
data x_new;
  set s3;
  a=put(col1,1.);
run;
data &nombre.;
  set cluster.&nombre. (where=(numcluster="&a."));
run;

Now without seeing anything else, this code looks syntactically correct - assuming a dataset s3 exists and contains a character field called col1.  This also assumes that in the library cluster there is a dataset called d_20500_ward_6 which contains a character variable called numcluster.  And it assumes there is a macro variable defined somewhere called a.  If all those assumptions are true then the code is fine.  However, I do not have a dataset called s3, a library called cluster nor a dataset called d_20500_ward_6 so I cannot say.  

Same with you second bit of code:

%let nombre=d_20500_Ward_6;
proc sql;
  /* this code doesn't do anything - just displays data in the output window */
  select  *    
  from    s3;

  create table s4 as 
  select  * 
  from    cluster.&nombre. 
  where   NUMCLUSTER="&a.";
quit;

Assuming you have a library called cluster and a dataset called d_20500_ward_6 in that libary which contains a character variable numcluster, then the code is good to go.  

 

What I am guessing is your problem - as you haven't posted any warning messages - is that the variable numcluster is actually numeric, in which case you need to change either of your codes to:

%let nombre=d_20500_Ward_6;
data x_new;
  set s3;
  a=put(col1,1.);
run;
data &nombre.;
  set CLUSTER.&nombre. (where=(numcluster=&a.));
run;


%let nombre=d_20500_Ward_6;
proc sql;
  /* this code doesn't do anything - just displays data in the output window */
  select  *    
  from    s3;

  create table s4 as 
  select  * 
  from    cluster.&nombre. 
  where   NUMCLUSTER=&a.;
quit;

 

mariange8282
Obsidian | Level 7
Thank you so much
Sabharish
Fluorite | Level 6
use input statement to change the variable x as numeric

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2397 views
  • 5 likes
  • 6 in conversation