topic Re: Cell info to variable in SAS Studio

Cell info to variable

mariange8282 — Fri, 14 Oct 2016 06:38:31 GMT

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

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

Thanks,

Re: Cell info to variable

Kurt_Bremser — Fri, 14 Oct 2016 06:41:13 GMT

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.

Re: Cell info to variable

RahulG — Fri, 14 Oct 2016 06:42:52 GMT

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
;

Re: Cell info to variable

mariange8282 — Fri, 14 Oct 2016 07:06:46 GMT

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.

Re: Cell info to variable

mariange8282 — Fri, 14 Oct 2016 07:15:10 GMT

The solution was:

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

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

Thanks

Re: Cell info to variable

Reeza — Fri, 14 Oct 2016 07:33:20 GMT

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

Re: Cell info to variable

Reeza — Fri, 14 Oct 2016 07:33:21 GMT

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

Re: Cell info to variable

RW9 — Fri, 14 Oct 2016 08:52:22 GMT

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.

Re: Cell info to variable

mariange8282 — Sat, 15 Oct 2016 11:54:22 GMT

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.

Re: Cell info to variable

Reeza — Sat, 15 Oct 2016 12:00:02 GMT

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;

Re: Cell info to variable

RW9 — Mon, 17 Oct 2016 08:34:08 GMT

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;

Re: Cell info to variable

mariange8282 — Tue, 25 Oct 2016 02:52:05 GMT

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?

Re: Cell info to variable

RW9 — Tue, 25 Oct 2016 08:18:23 GMT

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;

Re: Cell info to variable

mariange8282 — Tue, 25 Oct 2016 14:05:31 GMT

Thank you so much

Re: Cell info to variable

Sabharish — Wed, 16 Nov 2016 07:12:33 GMT

use input statement to change the variable x as numeric