topic Re: update query in SAS Programming

update query

NazaninSAS — Thu, 02 Aug 2018 18:03:05 GMT

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

Re: update query

novinosrin — Thu, 02 Aug 2018 18:08:58 GMT

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!

Re: update query

NazaninSAS — Thu, 02 Aug 2018 18:28:02 GMT

	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.

Re: update query

novinosrin — Thu, 02 Aug 2018 18:29:53 GMT

do you mean this?

data want;

set have;

if not missing(col1) then col2=col1;

run;

Re: update query

NazaninSAS — Thu, 02 Aug 2018 18:32:46 GMT

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

Re: update query

NazaninSAS — Thu, 02 Aug 2018 18:34:41 GMT

I wrote this, but it doesn't work

data Update;

set SINewCol;

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

run;

Re: update query

novinosrin — Thu, 02 Aug 2018 18:36:47 GMT

data have;
input col1;
cards;
1
.
2
.
3
.
4
;

data want;
set have;
if not missing(col1) then col2='Y';
run;

Re: update query

novinosrin — Thu, 02 Aug 2018 18:39:12 GMT

or you can also write the above as

if col1 then col2='Y';

just another variant of the same

Re: update query

NazaninSAS — Thu, 02 Aug 2018 18:54:01 GMT

I don't want to use cards.

what should I use instead?

Re: update query

novinosrin — Thu, 02 Aug 2018 18:57:48 GMT

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

Re: update query

NazaninSAS — Thu, 02 Aug 2018 18:59:55 GMT

This is what I have but it gives me error

data update;

set Sinewcol;

if (DIS1) then PWD='Y';

run;

Re: update query

novinosrin — Thu, 02 Aug 2018 19:01:47 GMT

what's the error, please post the log report

Re: update query

NazaninSAS — Thu, 02 Aug 2018 19:05:55 GMT

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;

Re: update query

novinosrin — Thu, 02 Aug 2018 19:12:31 GMT

try

if DIS1 then PWD='Y';

instead of

if (DIS1) then PWD='Y';

though i don't think that would make any difference

Re: update query

NazaninSAS — Thu, 02 Aug 2018 19:13:26 GMT

I tried both.

no difference

Re: update query

PeterClemmensen — Thu, 02 Aug 2018 19:13:32 GMT

@NazaninSAS, this is not an error. It is a note. You can read more about that particular note in a related thread here.

https://communities.sas.com/t5/Base-SAS-Programming/quot-NOTE-49-169-The-meaning-of-an-identifier-after-a-quoted/td-p/121796

Re: update query

novinosrin — Thu, 02 Aug 2018 19:18:31 GMT

try another variant:

data want;
set have;
if col1>. then col2='Y';*if col1 is numeric;
/*if col1>' ' then col2='Y';*if col1 is character;*/
run;

Re: update query

NazaninSAS — Thu, 02 Aug 2018 19:30:40 GMT

no luck!! 😞

Re: update query

novinosrin — Thu, 02 Aug 2018 19:39:55 GMT

I am afraid you really need to share the best sample of what dataset you have for the community to test. More exact or close to being exact will help

Re: update query

hashman — Thu, 02 Aug 2018 21:56:20 GMT

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