Write and run SAS programs in your web browser

Cell info to variable

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Cell info to variable

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, 


Accepted Solutions
Solution
‎10-25-2016 10:04 AM
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Cell info to variable

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


All Replies
Esteemed Advisor
Posts: 5,991

Re: Cell info to variable

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 227

Re: Cell info to variable

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
;
Contributor
Posts: 23

Re: Cell info to variable

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. 

Contributor
Posts: 23

Re: Cell info to variable

The solution was:

 

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

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

 

Thanks

Grand Advisor
Posts: 16,411

Re: Cell info to variable

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

 

Grand Advisor
Posts: 16,411

Re: Cell info to variable

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

 

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Cell info to variable

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.

 

Contributor
Posts: 23

Re: Cell info to variable

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.

Grand Advisor
Posts: 16,411

Re: Cell info to variable

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;  

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Cell info to variable

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;

 

Contributor
Posts: 23

Re: Cell info to variable

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?

Solution
‎10-25-2016 10:04 AM
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Cell info to variable

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;

 

Contributor
Posts: 23

Re: Cell info to variable

Thank you so much
Occasional Contributor
Posts: 12

Re: Cell info to variable

use input statement to change the variable x as numeric
Post a Question
Discussion Stats
  • 14 replies
  • 466 views
  • 5 likes
  • 6 in conversation