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 133953 articles. 0 followers. Post Follow

4 Replies
1136 Views

Similar Articles

[PageSpeed] 8
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:

GLM, categorical variable, interaction
Hello. I am trying to figure out how to do the following in GLM univariate in SPSS: I have a continuous DV and a categorical IV (group A or group B) and I want to test the interaction of another continuous variable while controlling for the effects of several variables that include a categorical variable (race) and an ordinal variable (education). I am confused as to how to enter the variables (fixed vs. random) and whether all variables that I intend to control for should be made covariates? Should the categorical IV be entered as fixed and the rest as covariates? Should the c...

pgsql: Correctly cast the return value of a CallableStatement when
Log Message: ----------- Correctly cast the return value of a CallableStatement when getShort is called. getByte presents a can't happen situation as no function can return a TINYINT because pg doesn't have an equivalent type. Make this throw an exception if we get to this point. Thanks to Christian Niles. Tags: ---- REL7_4_STABLE Modified Files: -------------- pgsql/src/interfaces/jdbc/org/postgresql/jdbc1: AbstractJdbc1Statement.java (r1.41.2.7 -> r1.41.2.8) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/jdbc/org/postgresq...

HELP
Hi, I've been asked to look at developing a new interface between Oracle and MS Sql Server. The setup is as follows. Oracle 9i installation on Solaris 9. Various standalone tables are populated with data via an application (periodically). The data in these tables needs to be copied to identical tables on a MS Sql Server DB running on Windows and accessed via a VPN. The data will only ever flow from Oracle to SQL. The tables are identical on both sides (compatible filetypes) and the update process will be carried out every 30 minutes (realtime not required). I need to develop an interf...

Re: Lament: character variable length limitation #7
Hi Mikeeee, it seems to be something with sports! I've found in (german) Wikipedia, that it is a socker club on the Faroe Islands. Cheers, Gerhard On Wed, 21 Oct 2009 15:36:56 -0400, Michael Raithel <michaelraithel@WESTAT.COM> wrote: >Dear SAS-L-ers, > >Kenneth M. Lin posted the following: > >> >> What's SI? >> >Kenneth, SI is the abbreviation for Sports Illustrated; check it out: www.si.com ! > >Kenneth, best of luck in all of your SAS endeavors! > > >I hope that this suggestion proves helpful now, and in the future! > >...

Replace
I have a string... str = "tyrtrbd =ffgtyuf == =tyryr =u=p ttttff" I want to replace the characters after each '=', what I ended up doing is somthing like this... buf = list(str) newchr = '#' count = 0 for i in range(len(buf)): if buf[count] == '=': buf[count + 1] = newchr count = count + 1 else: count = count + 1 newstr = ''.join(buf) Is there a better, faster way of doing it? Using somthing like str.index() dosn't work because... str = "hello world" for i in str: print str.index(i) 0 1 2 2 4 5 6 4 8 2 10 Every...

Re: Forcing SAS to print the resolved value of macro variables in #2
Scott, The simple answer. 1 options mprint; 2 %let foo=bar; 3 options symbolgen ; SYMBOLGEN: Macro variable FOO resolves to bar 4 title "&foo"; SYMBOLGEN: Macro variable FOO resolves to bar 5 data &foo ; 6 &foo = "&foo" ; SYMBOLGEN: Macro variable FOO resolves to bar SYMBOLGEN: Macro variable FOO resolves to bar 7 run ; NOTE: The data set WORK.BAR has 1 observations and 1 variables. The following is based on "Make Your Own Macro Processor" in NESUG 1999 hints at a more sophisticated...

Variable names
About a year ago, I wrote a simple type enforcement library that adds a should_be method to the Object class. This method looks like this: def should_be(types, varName) # types is either a Class or an array of Classes # that are legal types (or super-types, if you will) # for self # varName is the symbol for the variable; we use this # when we throw the exception <type checking code> # we throw an exception if self is not of one of # the types specified end So, say we have a method print_line that takes two parameters (1) an integer line number and (2)...

Prevent ruby constant variables from changing?
Hi, I understand that ruby constant variables are declared by using uppercase letter. However, the constant variable can still be reassigned even though a warning is issued. Is there a way I can fix the value that is initially assigned to the constant variable? Thanks -- Posted via http://www.ruby-forum.com/. George Wang wrote: > However, the constant variable can still be > reassigned even though a warning is issued. Am I the only person who thinks that constants should be unchangeable? Being new to Ruby, it seems weird to me that constants aren't actually constant. I'...

Mean of 2 variables or more
Hey, I want to have the mean of 2 variables or more, i use compute, but there are some numbers i want to be excluded so only 1 to 5 and not 6, now i have a 3 and a 6 but he says it's 4,5, but i want him to calculate 3. I already used selct cases but i forgetting something and i don't no what?? ...

Replacement for xgrabsc
Hi, During a recent upgrade we changed computers from B2000 to C8000. Now xgrabsc does not work since the graphics card (ATI Fire GL T2, 24 bits) is not supported. I wonder if anyone can recommend a HPUX replacement that will work with my graphics card. I need to be able to specify the window ID on the command line, and the program should output color postscript. Thanks, Johan In article <1152753178.655459.157920@m73g2000cwd.googlegroups.com>, j.berntsson@student.qut.edu.au writes: > Hi, > > During a recent upgrade we changed computers from B2000 to C8000. Now > xgrabsc...

gsfmode=replace does not replace
I am using the goption gsfmode=replace and I am expecting that if you use name="xxx" on the ganno statement then when you come to run it a second time it will "replace" what it wrote before and reuse the name "xxx". But it does not. This is true for sas v8.2 and sas v9.1.3 . What is wrong? How can I get it to reuse the name I have defined to name= on "proc ganno"? On Mar 6, 8:50=A0pm, RolandRB <rolandbe...@hotmail.com> wrote: > I am using the goption gsfmode=3Dreplace and I am expecting that if you > use name=3D"xxx" on the ganno s...

US-TX-Austin: SW Developer, 7-10yrs exp., 3yrs C++, OOD, Oracle, SQL, XML; C-H (45332057606)
US-TX-Austin: SW Developer, 7-10yrs exp., 3yrs C++, OOD, Oracle, SQL, XML; C-H (45332057606) ============================================================================================ Position: SW Developer Reference: SMC01795 Location: Austin TX Duration: C-H Skills: 7-10yrs SW Development Experience 3+ys C++ Advanced object-oriented development experience Cross-platform development on Windows, Solaris, and HP-UX required Basic Oracle and SQL Server experience required Ex...

Dell Inspiron 8200 Fan Will not work even after replacing
I have a Dell Inspiron 8200 and which has the dual fans on the back. One of the fans stopped and the other was always running at a very high speed to keep up. So I purchased a new fan for it and replaced it but now neither fan works at all. I put the old bad fan back in and neither of them work on that one now. The computer boots up fine but I shut it down right away to avoid over heating. I also purchased a third fan just to be sure with no luck on that one either. Any help would be great. In news:5fa62afe-0623-48a7-a8cc-0b6fc742433a@s8g2000prg.googlegroups.com, toddw81@gmail.com typed...

Why define-values is not in r6rs?
Hi, Recently I was not able to find (via google) reasons why define-values is not in r6rs. Is it kind of dangerous form or it was just difficult to predict consequences ? Following use case can show why I think it is useful: (define-values (make-instance instance?) (let ([instance-tag '(instance-tag)]) ;; make a unique tag within a local scope. ;; no other data object is eq? to this tag. (values (lambda (dispatcher) (cons instance-tag dispatcher)) (lambda (obj) (and (pair? obj) (eq? (car obj) instance- tag)))))) Thanks ahead. -- Bohdan Perhap...

Stop format from collapsing rows in proc tabulate?
Hi there, The following piece of code demonstrates what my problem is: data xx; input cat trt val; cards; 1 1 1 2 1 2 3 2 4 4 2 5 ; proc format; value cat 1,3='cat 1' 2,4='cat 2' ; proc tabulate data=xx missing noseps; class cat trt; var val; table cat='', trt=''*val=''*sum=''; *format cat cat.; run; Run above code with format statement off result in this: -------------------------------------------------- | | 1 | 2 | |----------------------+------------+------------| |1 | ...

[News] Value of Proprietary Software Certification Plunges
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 SAP certifications boosting pay, but others still falling: survey ,----[ Quote ] | For instance, IT certifications among those with the largest market value | declines in the past 12 months included Microsoft Certified | Professional+Internet (MCP+I) with a 40% decrease. Pay for IBM Certified | Advanced Application Developer -- Lotus Notes/Domino, Novell/Certified | Internet Professional (CIP) and Novell/Certified Novell Engineer (CNE) each | shrunk by 25%. `---- http://www.networkworld.com/news/2008/091208-sap-cert.html Recent: Re...

replacing Strings (again)
Hallo, I have a string with lots of numbers in scientific notation, e.g. 1,23456E+10 (German OS). I want to save it to a spreadsheat file, but before that I want to replace all "," with "." and all "E" with "e". (Otherwise GNUplot cannot read them). With my idea and the ideas of the LabVIEW-Forum, only the first ones were replaced. Example: 1,0E-6 1,0E-6 becomes 1.0e-6 1,0E-6 and NOT 1.0e-6 1.0e-6 How can I manage that? The common "Search and Replace String" works but stops after the first match. Arno PS: Please send VI's that can be l...

efficency for not instanciating variables?
Hi, is this code: class XY{}; void foo(bool b) { XY a; if(b) { XY c; c.Dosomething(); } } more efficient than: void foo(bool b) { XY a, c; if(b) { c.Dosomething(); } } if (!b), or will the compiler (gcc 3.3.3+, VC7.1) recognize it and produce the same code? Because I'm always writing such code and am not sure if the compiler would have been smart enough to do that itself. -- -Gernot int main(int argc, char** argv) {printf ("%silto%c%cf%cgl%ssic%ccom%c", "ma", 58, 'g', 64, "ba", 46, 10);} Ger...

theoretical F-value
Dear all, I need the function to compute the theoretical F-value due to the alpha level, the degree of freedom and the number of samples. The statistical toolbox is available. Thanx in advance ...

Re: creat time to event variable #2
jennifer_rose@BROWN.EDU wrote: > >Hi, > >I have 30-day timeline follow back data, and I need to create a time to >first heavy drinking day variable. Right now, each subject has a single >record with 30 variables (representing each day of the 30-day timeline >follow back) and a subject ID variable (SID). The 30 variables are coded 0 >if no heavy drinking on that day and 1 if heavy drinking occured on that >day. I would like to create a time variable to use with proc lifereg to >create a kaplan-meier curve for number of days to first heavy drinking >day. Can anyo...

Data Analyst
$50-65k + benefits, vacation and bonus of 0-8% Data Operations Analyst Our client is a relationship management firm with operations in Toronto and New York. We aid our clients in creating value through relationship management. Our client' programs aim to establish a mutual exchange of value between consumer needs and client objectives. As a Data Operations Analyst, you will play a hands-on role actively managing the data production processes, generating scripts and reports, automating repetitive tasks and supporting the Analysis and Web Development teams. The ideal candidat...

solid state disk replacement for RD53, 54, RK05, RL02/02??
I am curious if anyone knows of a source for a SSD for anyone of the above drives? I did some searching on the net and came up with a few 'experiments' that were 80-90% working, but could not find anything that said something to the effect that 'I did this and I use it daily on my xxx system to replace my yyy drive - don't have any problems'.... I am toying with the idea of making one - I am a hw type and have the equipment I need to do it. Thanks John On 13 Feb, 18:42, jjh <jjhu...@gmail.com> wrote: > I am curious if anyone knows of a source for a S...

regex in perl (using variables)
How do I make this work!!! $head_ ="Subject: Get cheap v i a g r a ..... "; #$rule is a variable which I used for reading text from a file! open (NWRULE, "<rule.spam"); @new_rule=<NWRULE>; close (NWRULE); Then I did sometning like this : foreach $rule(@new_rule) { if($rule =~ /(\S+) (\S+) ([^\n]+)/) { $new_id=$1; $dio=$2; $reg=$3; if($head_ =~ m/$reg/) { print "something\n"; } ..... Content of a file rule.spam is : new_1 head Subject: .*\.\. dario wrote: > How do I make this work!!! <fragmentary code snipped> Please post a _...

MACRO to replace IMUL with LEA, SHL, ADD
I've created a "mult" macro that replaces (in some cases) the slower "imul" instruction. (max 5 times faster than imul) Currently, it supports all numbers between 0 and 50, and some others. You can get it from www.beotel.net/~astancic (click on "Multiply.inc") -- Generic Window Handler - easy dialogs & MDI - no MFC www.beotel.net/~gwh On Jul 29, 4:34 am, aleksa <spamt...@crayne.org> wrote: > I've created a "mult" macro that replaces (in some cases) > the slower "imul" instruction. (max 5 times faster than imul) >...

API parameter values
I have not used API's so far. I need to retrieve back the attributes of a remote printer (an IP based printer configured on the 400 to an output queue) using QGYRPRTA. I use CL to call the API: CALL PGM (QGYRPRTA) PARM (''&RCVINF'' 50 ''RPTA0100'' ''DEV040'' '' '') I get the output as blank in the receive variable (&RCVINF) . Can anyone help me with samples of calling API and parameter passing. Thank you, Gai Character literals are denoted by apostrophes, and the Variable name in a CLP is an ampersan...