Help using Base SAS procedures

to chk if corresponding variable has perm val if the variable has all misng values then drop column

Reply
Regular Contributor
Posts: 190

to chk if corresponding variable has perm val if the variable has all misng values then drop column

[ Edited ]

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);

 

Super User
Super User
Posts: 7,942

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

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?

Regular Contributor
Posts: 190

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

[ Edited ]


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);

Regular Contributor
Posts: 190

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

[ Edited ]

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

Super User
Super User
Posts: 7,942

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

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;
Regular Contributor
Posts: 190

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

[ Edited ]

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

Respected Advisor
Posts: 3,799

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

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

 

 

Respected Advisor
Posts: 3,799

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

[ Edited ]
Posted in reply to data_null__

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

Regular Contributor
Posts: 190

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

[ Edited ]
Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

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.

Regular Contributor
Posts: 190

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

Posted in reply to data_null__
@Data_null_ y isn't test also dropped it also has all missing values?
Respected Advisor
Posts: 3,799

Re: Checking whether the DS has sdtm variable perm if it has missing values then drop the column

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

Super User
Posts: 10,020

Re: to chk if corresponding variable has perm val if the variable has all misng values then drop col

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)
Ask a Question
Discussion stats
  • 12 replies
  • 495 views
  • 0 likes
  • 4 in conversation