BookmarkSubscribeRSS Feed
RTelang
Fluorite | Level 6

am passing a DS in macro parameter with var_core_val=perm if its corresponding variable has same value but the variables has all missing values then drop it, in my code dept has var core val = prem & its variables are missing how do i drop it in my macro?


DATA details; INPUT id name $ dept $ salary; datalines; 01 John . 10000 02 Mary . 20000 03 Priya . 30000 05 Ram . 25000

;

DATA newdetails;
INPUT var_name $ var_core $;
DATALINES;
id perm
name perm
dept perm
salary req

;

      %macro core_check(inds=,newds=,var_core_val= );
      proc sql noprint;
      select var_name 
      into :varname separated by ' '
      from &newds
      where var_core="&var_core_val.";
      quit;

      %let nvar=&sqlobs;
      %put &varname;

     %do i=1 %to &nvar;
     %let var&i=%scan(&varname,&i);
     proc sql;
     select count(*)
     into :nobs
     from &inds where &&var&i is not missing ;
     %put this = &nobs;
     quit;
     %end;
     %mend;
     %core_check(inds=work.details,newds=work.newdetails,var_core_val=perm);

 

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please clarify your question, post test data in the form of a datastep, and what you want the output to look like.  I do not see what that macro code is doing, all it seems to do is create two coped of the imported file - test1 test2.  Then another copy of the same dataset with a filter on a variable var_core="Perm".  So what is all that code for?

RTelang
Fluorite | Level 6


DATA details;
INPUT id name $ dept $ salary;
datalines;
01 John . 10000
02 Mary . 20000
03 Priya . 30000
05 Ram . 25000
;


DATA newdetails;
INPUT var_name $ var_core $;
DATALINES;
id perm
name perm
dept perm
salary req
;


%macro core_check(inds=,newds=,var_core_val= );
proc sql;
select var_name
into :varname separated by ' '
from &newds
where var_core="&var_core_val.";
quit;
%put &varname;
%let nvar=&sqlobs;
%do i=1 %to &nvar;
%let var&i=%scan(&varname,&i);
proc sql;
select count(*)
into :nobs
from details where &&var&i is not missing;
%put &nobs;
quit;
%end;
%mend;
%core_check(inds=work.details,newds=work.newdetails,var_core_val=perm);

RTelang
Fluorite | Level 6

heres my code how can i delete dept as it has missing values

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Ok, so what you are looking at is the CDISC SDTM model.  For that you need to build a define.xml document which details what domains the model contains, and what each of the models contain.  It should be at that point, when you define the model, that variables which will never have data can be removed, it shouldn't be interactive based on the data.  For example, say in the first transfer of SDTM DB, I get a domain LB which has PARAM (this is an example to illustrate the point, not real), which is perm, but as its not coded at the first transfer, it doesn't appear in the domain per your coding.  I look at the define document, and the transfer agreement and either fail the whole things due to missing elements, or it doesn't flag and my subsquent programming is not correct as next time that variable does appear.

 

To clarify, the base point of programming is documentation.  Create your specifications first - this being the define - once that is agreed and approved, then you start programming based on that documentation.  

 

As to your specific question, simple code like the one below can be used to identify a list of values to drop (do note the use of the code window - {i} from above the post, consistent formatting, indetation, finishing blocks of code etc. which is even more important when building generic library code) although I would still suggest that going this route will end in tears:

data details;
  input id name $ dept $ salary;
datalines;
01 John . 10000
02 Mary . 20000
03 Priya . 30000
05 Ram . 25000
;
run;

data newdetails;
  input var_name $ var_core $;
datalines;
id req
name perm
dept perm
salary req
;
run;

proc sql;
  create table TO_DROP (VAR_NAME char(200));
quit;

data _null_;
  set newdetails (where=(var_core ne "req"));
  call execute('proc sql;  insert into TO_DROP select distinct "'||var_name||'" from DETAILS where '||var_name||' is null; quit;');
run;
RTelang
Fluorite | Level 6

@RW9can u suggest me a step by which we can drop column (Dept) having all missing values? i.e Dept in my case how can i incorporate it in my Macro?

data_null__
Jade | Level 19

In SAS vernacular it is DROP.  Variables are dropped records are deleted.

 

 

data_null__
Jade | Level 19

I think this is what you want.  

 

  • If a variable has only missing values and
  • if it is NOT(req) from the meta data
  • then DROP if from the data data-set.  

 

Depending on your data and the method used, checking for all missing can be time consuming.  For the variables you are looking for you have to look at every value.  This method I show is easy to program and efficient enough until your data get big LB, EG etc.

 

 

 

data details;
   input id name $ dept $ salary;
   test=' ';
   datalines;
01 John . 10000
02 Mary . 20000
03 Priya . 30000
05 Ram . 25000
;
run;
proc print;
   run;
data meta;
   input var_name $ var_core $;
   datalines;
id req
name perm
dept perm
salary req
test req
;
run;
proc print;
   run;
%let req=;
proc sql;
   select var_name into :req separated by ' ' from meta where var_core eq 'req';
   quit;
%put NOTE: &=req;
proc format;
   value $_chr_ ' '=' ' other='1';
   value _num_ ._-.z=' ' other='1';
   run;
ods select nlevels;
ods output nlevels=nlevels;
proc freq data=details(drop=&req) nlevels;
   format _character_ $_chr_. _numeric_ _num_.;
   run;
ods select all;
data nlevels(keep=tablevar);
   length NLevels NMissLevels NNonMissLevels 8;
   set nlevels;
   if nnonmisslevels le 0;
   run;
proc print;
   run;
%let droplist=;
proc sql;
   select tablevar into :droplist separated by ' ' from nlevels;
   quit;
%put NOTE: &=droplist;
data newdetails;
   set details(drop=&droplist);
   run;
proc print;
   run;

Capture.PNG

RTelang
Fluorite | Level 6

@data_null__ i want i.e if variable has all missing values and is has same Perm value from the meta data
then DROP if from the data data-set. i.e Dept column in my data so how i code it to drop it inside my macro.

data_null__
Jade | Level 19

You need to add code to your macro to determine the names of all variables that are 'PERM' and have missing values on every record.  Once you have that list of variables use it in a DROP data set option or DROP statement to create a new data set.

 

If you have time to look at the program I posted you'll see that it does exactly that.

RTelang
Fluorite | Level 6
@Data_null_ y isn't test also dropped it also has all missing values?
data_null__
Jade | Level 19

@RTelangIf a variable is REG in the meta data table is not included in the test for missing values.

Ksharp
Super User

CODE NOT TESTED.

 

data details;
   input id name $ dept $ salary;
   test=' ';
   datalines;
01 John . 10000
02 Mary . 20000
03 Priya . 30000
05 Ram . 25000
;
run;

data meta;
   input var_name $ var_core $;
   datalines;
id req
name perm
dept perm
salary req
test req
;
run;

%macro core_check(inds=,newds=,var_core_val= );
    proc sql noprint;
      select distinct 'n('||strip(var_name)||') as '||var_name 
      into : varname separated by ','
      from &newds
      where var_core="&var_core_val.";
    

     create table temp as
     select &varname 
     from &inds  ;
     
    quit;
    
    proc transpose data=temp out=count;
    run;
    
    proc sql;
     select _name_ label='Variables for all missing value'
      from count
       where col1=0;
    quit;
    
     %end;
     %mend;
     %core_check(inds=work.details,newds=work.newdetails,var_core_val=perm)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1245 views
  • 0 likes
  • 4 in conversation