Replacing variable values with proc sql

  • Permalink
  • submit to reddit
  • Email
  • Follow


Hi SAS-L users!

I was wondering if there is a possibility to replace values of a variable
with proc sql. The data step would look like the following:

data test;
  set test;
  if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql (without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply dassybr (6) 2/8/2006 8:16:37 AM

See related articles to this posting


On Wed, 8 Feb 2006 03:16:37 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM>
wrote:

>Hi SAS-L users!
>
>I was wondering if there is a possibility to replace values of a variable
>with proc sql. The data step would look like the following:
>
>data test;
>  set test;
>  if var=' ' then var='No Value';
>run;
>
>The if and then statement is what I am looking for in proc sql (without
>creating a macro).
>
>Thanks a lot for any comments.
>
>Hadassa

Yes there are a few ways:


data test;
aa='1';
bb='2';
cc=' ';
run;

missing values are easiest replaced
with the coalesce function, like this:

proc sql;
 select aa, bb, coalesce(cc,'Missing')
 from test;
quit;

Case expressions can also do that, and a lot more.
As a general rule, check out the documentation!

proc sql;
 select aa, bb,
  case
   when cc is missing then 'Missing'
   else cc
  end as cc
 from test;
quit;
0
Reply sas__l (62) 2/8/2006 8:41:57 AM

Hi Hadassa Brunschwig.

Here is the SQL code for replacing data


data test;
infile cards missover;
input id name$ city$ ;
cards;
1 A YN
2 B BK
3 C IN
4 D
5 Y   ;RUN;

proc sql;
create table test as
select id, name, coalesce(city,'No Info') 
 from test; 
quit;

0
Reply ganesh.makireddy (3) 2/8/2006 10:24:29 AM

Hadassa:

  You can use the following:

proc sql;
  Update test
      set var='No Value'
      where var is missing;
quit;

J S Huang
1-515-557-3987
fax 1-515-557-2422

>>> Hadassa Brunschwig <dassybr@HOTMAIL.COM> 2/8/2006 2:16:37 AM >>>
Hi SAS-L users!

I was wondering if there is a possibility to replace values of a
variable
with proc sql. The data step would look like the following:

data test;
  set test;
  if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql
(without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply jiann-shiun.huang (548) 2/8/2006 1:55:14 PM

Hadassa,

You looking for the coalesce function, incidently you can use it in the data
step too.



Toby Dunn





From: Hadassa Brunschwig <dassybr@HOTMAIL.COM>
Reply-To: Hadassa Brunschwig <dassybr@HOTMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Replacing variable values with proc sql
Date: Wed, 8 Feb 2006 03:16:37 -0500


Hi SAS-L users!

I was wondering if there is a possibility to replace values of a variable
with proc sql. The data step would look like the following:

data test;
   set test;
   if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql (without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply tobydunn (6018) 2/8/2006 2:29:38 PM
comp.soft-sys.sas 131473 articles. 30 followers. Post

4 Replies
728 Views

Similar Articles

[PageSpeed] 15


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Re: Replacing variable values with proc sql
On Wed, 8 Feb 2006 03:16:37 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM> wrote: >Hi SAS-L users! > >I was wondering if there is a possibility to replace values of a variable >with proc sql. The data step would look like the following: > >data test; > set test; > if var=' ' then var='No Value'; >run; > >The if and then statement is what I am looking for in proc sql (without >creating a macro). > >Thanks a lot for any comments. > >Hadassa Yes there are a few ways: data test; aa='1'; bb='2'; cc=' &#...

Re: Replacing variable values with proc sql #2
> Date: Wed, 8 Feb 2006 04:25:49 -0500> From: dassybr@HOTMAIL.COM> Subject:= Re: Replacing variable values with proc sql> To: SAS-L@LISTSERV.UGA.EDU> >= Thanks for your comment!> The problem is that I have a dataset with many v= ariables and it would be> crazy to list them all. So if I do:> > create tab= le test;> select *, case> when cc is missing then 'Missing'> from dataset;>= quit;> > I get a warning that cc already exists in the dataset(which is co= rrect) and> the missing value of cc is not replaced.> (Bummer...)> > A...

Re: Replacing variable values with proc sql #3
Thanks for your comment! The problem is that I have a dataset with many variables and it would be crazy to list them all. So if I do: create table test; select *, case when cc is missing then 'Missing' from dataset; quit; I get a warning that cc already exists in the dataset(which is correct) and the missing value of cc is not replaced. (Bummer...) Any comments on that ;-)? Hi Hadassa Brunschwig, Use this code and let me know if you have any problem...., I hope it vl use to you proc sql; create table test as select *, coalesce(city,'No Info') from test; quit; data...

Re: Replacing variable values with proc sql #4
> Date: Wed, 8 Feb 2006 04:25:49 -0500> From: dassybr@HOTMAIL.COM> Subject:= Re: Replacing variable values with proc sql> To: SAS-L@LISTSERV.UGA.EDU> >= Thanks for your comment!> The problem is that I have a dataset with many v= ariables and it would be> crazy to list them all. So if I do:> > create tab= le test;> select *, case> when cc is missing then 'Missing'> from dataset;>= quit;> > I get a warning that cc already exists in the dataset(which is co= rrect) and> the missing value of cc is not replaced.> (Bummer...)> > A...

Re: Replacing variable values with proc sql #7
dassybr@HOTMAIL.COM wrote: >Thanks for your comment! >The problem is that I have a dataset with many variables and it would be >crazy to list them all. So if I do: > >create table test; >select *, case > when cc is missing then 'Missing' >from dataset; >quit; > >I get a warning that cc already exists in the dataset(which is correct) and >the missing value of cc is not replaced. >(Bummer...) > >Any comments on that ;-)? I have one. I recommend that you totally skip this entire process. Really. All you're doing is assigning a new...

Re: Replacing variable values with proc sql #8 1551481
Hadassa, You looking for the coalesce function, incidently you can use it in the data step too. Toby Dunn From: Hadassa Brunschwig <dassybr@HOTMAIL.COM> Reply-To: Hadassa Brunschwig <dassybr@HOTMAIL.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Replacing variable values with proc sql Date: Wed, 8 Feb 2006 03:16:37 -0500 Hi SAS-L users! I was wondering if there is a possibility to replace values of a variable with proc sql. The data step would look like the following: data test; set test; if var=' ' then var='No Value'; run; The if and then statement i...

Re: Replacing variable values with proc sql #8 660751
A truly quick-and-dirty fix is to switch the SELECT list around so that the replacement column appears first, as in create table test; select case when cc is missing then 'Missing' else cc end as cc, * from dataset; You will still get the warning, but the new CC column will be the one which survives the name collision. On Wed, 8 Feb 2006 04:25:49 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM> wrote: >Thanks for your comment! >The problem is that I have a dataset with many variables and it would be >crazy to list them ...

Re: Replacing variable values with proc sql #9 660755
This is one area where I personally see using data step options keep and rename with sql: Proc Sql ; create table test ( drop= _cc ) as select * , Coalesce( _cc , 'Missing') as CC from dataset ( rename = ( cc = _cc ) ) ; quit ; Toby Dunn From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Replacing variable values with proc sql Date: Wed, 8 Feb 2006 23:06:34 -0500 A truly quick-and-dirty fix is to swit...

Re: Replacing variable values with proc sql #9 1551485
Hadassa: You can use the following: proc sql; Update test set var='No Value' where var is missing; quit; J S Huang 1-515-557-3987 fax 1-515-557-2422 >>> Hadassa Brunschwig <dassybr@HOTMAIL.COM> 2/8/2006 2:16:37 AM >>> Hi SAS-L users! I was wondering if there is a possibility to replace values of a variable with proc sql. The data step would look like the following: data test; set test; if var=' ' then var='No Value'; run; The if and then statement is what I am looking for in proc sql (without creating a macro). Thanks ...

proc sql: variable values into macro variable
Hi, all I have a data set containing a character variable (replay) and want to concatenate all the values into a macro variable. I know this is an easy task for PROC SQL, but can't find the right syntax. I.e., from the dataset: panel plot replay 1 1 1:1 4 2 4:2 2 3 2:3 5 4 5:4 3 5 3:5 6 6 6:6 I want to create a macro variable, say replay, that would be equivalent to %let replay = 1:1 4:2 2:3 5:4 3:5 6:6; best, -Michael -- Michael Friendly Email: friendly@yorku.ca Pro...

Re: proc sql: variable values into macro variable
proc sql noprint; select replay into :replay separated by ' ' from dataset ; quit; %Put &replay; -----Original Message----- From: Michael Friendly [mailto:friendly@yorku.ca] Sent: Thursday, December 11, 2003 5:23 PM To: SAS-L@LISTSERV.UGA.EDU Subject: proc sql: variable values into macro variable Hi, all I have a data set containing a character variable (replay) and want to concatenate all the values into a macro variable. I...

Re: proc sql: variable values into macro variable #4
While it is much simpler to do it in SQL, it is possible in a data step too. This solution is limited by the maximum length possible for a character variable. data _null_ ; set <input data> end = eof ; length t $32767 ; retain t ; t = trim(t) || " " || replay ; if eof then call symput("replay",trim(left(t))) ; run ; _________________________________ Venky Chakravarthy E-mail: swovcc_AT_hotmail_DOT_com -----Original Message----- From: Michael Friendly [mailto:friendly@YORKU.CA] Sent: Thursday, December 11, 2003 5:23 PM To: SAS-L@LISTSERV.UGA.EDU Subje...

Re: proc sql: variable values into macro variable #3 625487
friendly@yorku.ca wrote: > I have a data set containing a character variable (replay) and > want to concatenate all the values into a macro variable. I > know this is an easy task for PROC SQL, but can't find the right > syntax. > . . . . > > I want to create a macro variable, say replay, that would be equivalent > to > > %let replay = 1:1 4:2 2:3 5:4 3:5 6:6; proc sql noprint; select replay into :mreplay separated by ' ' from work.test; quit; HTH, David -- David Cassell, CSC Cassell.David@epa.gov Senior computing specialist mathematical statis...

Re: proc sql: variable values into macro variable #3 1541167
Hi Michael, data test; input panel plot replay $; cards; 1 1 1:1 4 2 4:2 2 3 2:3 5 4 5:4 3 5 3:5 6 6 6:6 ; run; proc sql noprint; select replay into :mvar separated by ' ' from test; quit; %put >&mvar<; Hope this is helpful, Mark Terjeson Reporting, Analysis, and Procurement Section Information Services Division Department of Social and Health Services State of Washington mailto:terjem@dshs.wa.gov -----Original Message----- From: Michael Friendly [mailto:friendly@YORKU.CA] Sent: Thursday, December 11, 2003 2:23 PM To: SAS-L@LISTSERV.UGA.EDU Subje...

Assigning value to a variable in proc sql
Hello all; I am using proc sql to join two datasets in SAS 9.1.3. The two datasets have a common variable 'category'. The variable has the value 'A' in one dataset and 'B' in the other. I would like to retain the variable in the new dataset but assign a new value, say 'C'. Any suggestions as to how this could be done? Kindly note that, I am not using this variable for merging and have other variables which are sufficient to merge the two datasets. Thanks and regards n n, All sort of derived mappings are possible: SELECT tab1.category as category, ... FR...

subsetting to unique values of a *single variable* in proc sql
hi all, My understanding is that 'distinct' in proc sql will subset to unique observations--rows--. How can I subset to the unique values of a single variable regardless of other variables in the same observation? example what I have: var1 var2 1 45% 2 45% 3 45% 4 45% What i want: var1 var2 value doesnt matter 45% (REGARDLESS of what value var1 will take upon subsetting) Thank you very much! Hala Hi Hala, You can use MONOTONIC() to get the desired result. data inp; input v1 v2 $ v3; cards; 1 10% 121 2 10% 111 3 10% 141 4 ...

PROC SQL: Concatenating values of several columns into several macro variables
Hello! I am using a proc sql statement like the one given below, which works fine: proc sql; select sum(z1),sum(z2),sum(z3) into :s1 separated by ' ', :s2 separated by ' ', :s3 separated by ' ' from dataset where event = 1 group by time; quit; What I want is that instead of having to specify for each variable " separated by ' ' ", I would like to have something like select sum(z1),sum(z2),sum(z3) into :s1-:s3 separated by ' ' since in general I have much more than 3 variables. However, that stat...

Way to select list of months value by using macro variable in PROC SQL
I come to following problem: I need to use PROC SQL to extract a list of attributes from a table. These attributes are like: a_200210 a200211...a_200504 In other words, their suffix are named for each month. I can't directly use following syntax in PROC SQL like PROC SQL; select table_name.* from table_name(keep= a_200210-a_200504); Because SAS think a_200213 should follow a_200212. Therefore I have to break them out, like (Keep=a_200210_a_200212 a_200301-a200312 a200401-....) But I need to put this code in a Macro. Therefore my start month can be changed. I want to use a macro varia...

Please check this SQL line for me
In a stored procede, I want to assign the return value of =91SELECT DATEADD(dd, -Day(GetDate()), GetDate())=92 to a variable. The following is what I have currently, but it does not work: SET @PreMonthEnd =3D SELECT DATEADD(dd, -Day(GetDate()), GetDate()) What will be the correct statement? Thanks. Anthony You do not need SELECT: SET @PreMonthEnd = DATEADD(dd, -DAY(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP); -- Plamen Ratchev http://www.SQLStudio.com Thank you so much, Plamen! That should work. (Would it work the same way as the following: SET @PreMonthEnd = (SELECT DATEADD(dd, -Da...

replacing variable values
Hi, I want to replace a variable value in a text file. the text file is cat propertyfile var1="value1" var2="value2" var3="value3" Now, i would like to replace $var1 in "propertyfile" using sed or awk. I want to change propertyfile to cat propertyfile var1="New val1" var2="New val2" var3="New val3" Thanks in advance, an On 2006-04-11, anju wrote: > Hi, > > I want to replace a variable value in a text file. the text file is > > cat propertyfile > var1="value1" > var2="value2" ...

proc sql
I have a libname setup which points to an ODBC connection which connects to a SQL Server database. Using this libname I am able to do a proc sql to query tables/views just fine. I have a stored procedure in the SQL database which returns a result set that I would like to call. Can somebody please help me with the syntax for this? Thanks. you probably need to use pass-through. Here is a piece of code I took from my blog and HTH. *************************************** * EXECUTE A STORED PROCEDURE ON SQL * * SERVER USING PASS-THROUGH * *************************************** ...

replace variable with same variable
I have a perl script where I match on a phrase to add something after that phrase: $line=~s/PILOT_INC=4,/PILOT_INC=4, NGHBR_SRCH_MODE = 0,/g; However, the "4" can be variable, say, anything between 2 and 8. I'd like to do something like this: $line=~s/PILOT_INC=\d,/PILOT_INC=\d, NGHBR_SRCH_MODE = 0,/g; Of course this won't work. Do I need to first save the number into a variable and then put that variable into the regex replace phrase or can it be done within the global search and replace regex line above? regards, David David wrote: > I have a perl script where I ...

what does this.value.replace().replace() do?
Hello, Amrit has posted the code below for keeping the focus on a text box until some text is entered. Can someone please explain how {if(this.value.replace(/^\s+/,"").replace(/\s+$/,"") == "") focus();} works - I don't understand the this.value.replace().replace() part.... Thanks Geoff function focus() { if(typeof document.MyForm.username.onblur != "function") document.MyForm.username.onblur = function() {if(this.value.replace(/^\s+/,"").replace(/\s+$/,"") == "") focus();} alert("please add y...

Why no Proc##[]=() ? Why no Proc##replace() ?
Hi, Looking at lvalues in Ruby I learned a lot. I thought Proc instances could be turned into lvalues too, so I tried: class Proc def []=(*args) self[*args] end end pp = proc { |*x| @val = x[0] unless x.empty? ; @val } pp[] = 1 # => 1 p pp[] # => 1 Ruby's cool, no doubts. Is this worth a RCR ? Then I tried to rebuilt my version of a Pointer class at http://www.c2.com/cgi/wiki?SinisterSchemeSampleInRuby with what I had learnt. class Pointer < Proc def initialize( &block ) ref( &block) end def ref( &block ) lvalue = block.call() tmp = eval( &qu...