topic checking null values for the variables in SAS Programming

checking null values for the variables

niteshbharadwaj — Wed, 19 Dec 2018 04:49:53 GMT

Hi All,

I have a dataset which consists of 3 columns name,memname and libname

memname consists of list of datasets and name consists of list variables associated to corresponding to dataset.

Kindly help me how to check whether the value of variable to particular dataset is null or not and populate a status varaible coulmn if value exists to a variable

only there are two libraries

ex.

memname: x1,x2,x3

name: v1,v2,v3

libname: l1,l2,l1

how to check whether vi is null or not

Re: checking null values for the variables [how to improve your question]

Community_Guide — Wed, 19 Dec 2018 04:44:53 GMT

Hello @niteshbharadwaj,

Your question requires more details before experts can help. Can you revise your question to include more information?

Review this checklist:

Specify a meaningful subject line for your topic. Avoid generic subjects like "need help," "SAS query," or "urgent."
When appropriate, provide sample data in text or DATA step format. See this article for one method you can use.
If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @Reeza

Re: checking null values for the variables

RW9 — Wed, 19 Dec 2018 09:36:58 GMT

Post example test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And show what you want the output from that test data to be.

You can loop over the datasets and run a proc freq on _character_ data, and proc means on _numeric_ data, which will give you a dataset of results. Or you can generate code from it. Neither way is however great. What value is there in this?

Re: checking null values for the variables

niteshbharadwaj — Wed, 19 Dec 2018 09:45:17 GMT

My dataset abcd has 3 coloumns:

memname name libname

x1 v1 lib1

x1 v2 lib1

x2 v3 lib2

x3 v4 lib1

This is how my dataset abcd looks like,

here memname varaible consists of list of datasets i.e. x1,x2,x3 are datasets

name variable consists of list of variables associated to it.

how to check whether varaiable v1 of dataset x1 is null or not.

Repeating the same process till end of the table.

and popultae a field status =0 for null and status =1 for not null in abcd dataset again

Re: checking null values for the variables

RW9 — Wed, 19 Dec 2018 10:05:01 GMT

Again,

Post example test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

We are not here to type in test data or guess what it looks like (and please supply test data for the ones to be checked, just one is fine).

At a guess:

data have;
  set have;
  count=0;
run;
data _null_;
  set have;
  call execute(cats('proc sql; update have set count=(select count(distinct case when ',name,' is null then 1 else 0 end) from ',libname,'.',memname,') where libname="',libname,'" and memname="',memname,'";'));
run;

I have nothing to test that on. It should generate 1 row of proc sql per row in the dataset, and those updates should insert a count into the dataset. If count=0 all are present, if > 0 then null.

Re: checking null values for the variables

niteshbharadwaj — Wed, 19 Dec 2018 10:55:57 GMT

please find the sample data

data abcd;
input memname $ name $ libname $;
cards;
x1 v1 l1
x1 v2 l2
x2 v3 l2
x3 v4 l1
;
run;

expected output:

data abcd5;
set abcd;
status2=0;
run;

here x1,x2,x3 are datasets corresponding to libraries l1 and l2

we want to check whether var v1 in dataset x1 is null or not

kindly accept apologies in case of inconvenience

Re: checking null values for the variables

RW9 — Wed, 19 Dec 2018 11:20:13 GMT

Ok, here you go. 0 indicates no missing data items, >0 is count of missings:

data abcd;
  input memname $ name $ libname $;
  status2=0;
cards;
class age work
class height work
;
run;

data class;
  set sashelp.class;
  if _n_ in (3,4) then age =.;
run;

data _null_;
  set abcd;
  call execute(cat('proc sql; 
                      update abcd 
                      set status2=(select sum(case when ',strip(name),' is null then 1 else 0 end) from ',catx('.',libname,memname),') where libname="',strip(libname),'" and memname="',strip(memname),'" and name="',strip(name),'";
                    quit;'));
run;