DATA Step, Macro, Functions and more

update query

Reply
Contributor
Posts: 24

update query

Hi experts,

 

in my dataset, I have a column with values and null. I need your guidance to write a code to check if there is no null, assign values to another column. For example, on C1, I have values, but I want to update my C2 where there is a value on C1.

 

Thanks a lot for your help.

 

Nazanin

Super User
Posts: 2,041

Re: update query

Posted in reply to NazaninSAS

Please post a sample of the data you want and a sample of data you want meaning what it looks like and what you want it or expect it to look like. Thank you!

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin
 

Col1

col2

1

  

2

25

Y

3

  

4

  

6

29

Y

7

  

9

  

 

Thanks,

so I want to update Col2, when Col1 is not null.

 

 

Super User
Posts: 2,041

Re: update query

Posted in reply to NazaninSAS

do you mean this?

 

data want;

set have;

if not missing(col1) then col2=col1;

run;

 

 

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin

if there is value on Col1, I want  to assign "Y" to Col2.

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin

I wrote this, but it doesn't work

 

data Update;

set SINewCol;

if not missing(Col1) then Col2='Y';

run;

Super User
Posts: 2,041

Re: update query

Posted in reply to NazaninSAS
data have;
input col1;
cards;
1
.
2
.
3
.
4
;

data want;
set have;
if not missing(col1) then col2='Y';
run;
Super User
Posts: 2,041

Re: update query

Posted in reply to novinosrin

or you can also write the above as

 

if col1 then col2='Y';

just another variant of the same 

Frequent Contributor
Posts: 112

Re: update query

Posted in reply to novinosrin

@novinosrin:

 

I love Boolean expressions but using one in this situation is a bit precarious. It would work perfectly well if the spec said "if COL1 is missing OR zero then value COL2". But because it says just "if COL1 has no value", it supposedly excludes COL1=0 since the laatter is a value. Hence, in this case confining the expression specifically to missing values only would be safer since it would mean making no assumptions about data.

 

Paul D.

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin

I don't want to use cards.

 

what should I use instead?

Super User
Posts: 2,041

Re: update query

Posted in reply to NazaninSAS

I just created a sample to test. That's the reason I named data have

 

The data want step actually is the code that you should use on your real dataset

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin

This is what I have but it gives me error

 

data update;

set Sinewcol;

if (DIS1) then PWD='Y';

run;

Super User
Posts: 2,041

Re: update query

Posted in reply to NazaninSAS

what's the error, please post the log report

Contributor
Posts: 24

Re: update query

Posted in reply to novinosrin

49

465! ;

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS

release. Inserting white space between a quoted string and the succeeding

identifier is recommended.

467 data update;

468 set Sinewcol;

469 if DIS1 then PWD = 'Y ';

470 run;

Super User
Posts: 2,041

Re: update query

[ Edited ]
Posted in reply to NazaninSAS

try 

if DIS1 then PWD='Y';

 

instead of

 

if (DIS1) then PWD='Y';

 

though i don't think that would make any difference

Ask a Question
Discussion stats
  • 25 replies
  • 112 views
  • 2 likes
  • 4 in conversation