checking null values for the variables

[niteshbharadwaj]

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

[Community_Guide]

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

.

[RW9]

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

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?  

[niteshbharadwaj]

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

[RW9]

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

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.

[niteshbharadwaj]

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

[RW9]

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;