Replacing variable values with proc sql

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
dassybr (6)
2/8/2006 8:16:37 AM
comp.soft-sys.sas 139128 articles. 1 followers. Post Follow

4 Replies
1765 Views

Similar Articles

[PageSpeed] 37

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
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
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
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
tobydunn (6018)
2/8/2006 2:29:38 PM
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 #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 #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 #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 #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 #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 #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 #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 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 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...

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...

Web resources about - Replacing variable values with proc sql - comp.soft-sys.sas

Facebook Tests Replacing Keyword Ad Targeting With Broad Category Targeting
Facebook is testing a major functionality change for its self-serve performance ad tool that would replace the specific Like keyword targeting ...

The Dangers of Replacing Coil Springs on Your Vehicle - Bonus Clip - YouTube
In this episode of Saturday Mechanic bonus clip, Ben and Russ show what happens when a coil spring compressor fails. Subscribe today for more! ...

School apologises for taking cane off blind boy and replacing it with pool noodle
A primary school has apologised for taking away the cane of an eight-year-old blind boy and replacing it swimming pool noddle as a form of punishment. ...

Federation Centres stuns market by replacing CEO Steven Sewell with Angus McNaughton effective immediately ...
Retail landlord Federation Centres has surprised the market with the&nbsp;appointment of&nbsp;former Novion Property's Angus McNaughton as the ...

Replacing China's broken economic gauge
As Chinese growth shifts away from its reliance on heavy industry, we need a new index to the track the ups and downs of the economy.

Inner south residents worried about high-rises replacing public housing flats
Residents say the&nbsp;government&nbsp;is over-developing its inner south blocks to maximise their sale value.

Apache Software Foundation unveils Hadoop 2, replacing MapReduce with YARN
The Apache Software Foundation unveiled its latest release of its open source data processing program, Hadoop 2. It runs multiple applications ...

Australia spends $110 million a year replacing lost coins
Australians lose 255 million coins every year - down the back of our couches, taken overseas in the pockets of tourists and stuck to footpaths. ...

Tweets Replacing Resumes - Twitter Becomes Job-Hunting Tool
Forget paper resumes, your next job could hang on a 140-letter tweet.

A case of one bad government replacing another
THERE is a widening gap between the sort of government a Tony Abbott-led Coalition will need to become to steer the nation successfully in the ...

Resources last updated: 1/28/2016 6:18:18 PM