Help using Base SAS procedures

A Data Manupulation Issue _ Thank you

Reply
Contributor
Posts: 44

A Data Manupulation Issue _ Thank you

I have a fllowing sample datasetwhich contains all character variables
(subject, F1, F2,F3, F4, F5, F6):

subject  F1   F2   F3   F4              F5    F6
-------  --   --   --  -----------     ----   -----------
10002     4    7   9    9               10    9
10001     0    3   4   Total (JD)        7    Total (PP)
10006     8    8   8    0                9    9

I need to delete those columns/vraiables if those vraiables have any value as "Total (JD)"
or "Total (PP)"


so the result dataset should be as below:

subject  F1   F2   F3     F5  
-------  --   --   --     --   
10002     4    7   9      10  
10001     0    3   4      7   
10006     8    8   8      9  

Means the remaining varibales would be (subject, F1, F2, F3, F5).

Thanks you so much from the bottom of my heart.

SAS Super FREQ
Posts: 708

Re: A Data Manupulation Issue _ Thank you

Posted in reply to need_sas_help

You can make use of arrays for this type of work, see sample code below. For each obs loop through all values and check for the text, if text is found get variable name, compare to a list and add if not found.

* test data ;
data have;
  infile cards dlm=",";
 
input
    subject :
$12.
    F1 :
$12.
    F2 :
$12.
    F3 :
$12.
    F4 :
$12.
    F5 :
$12.
    F6 :
$12.
  ;
  cards;
10002,     4,    7,   9,    9,               10,    9
10001,     0,    3,   4,   Total (JD),        7,    Total (PP)
10006,     8,    8,   8,    0,                9,    9
10007,     8,    8,   8,    Total (JD),       9,    9
10008,     8,    8,   8,    0,                Total (PP),  9
;

* find all variables with some text ;
data _null_;
  set have end=last;
  array xchar{*} f1 - f6;
  length dropList $ 1024;
 
retain dropList;
  drop i;

 
* loop through all vars in array and check for text
  * if text is found check if we know this variable already
  * otherwise add it to the list
  *;

 
do i = 1 to dim( xchar );
    if upcase( xchar{i} ) in ("TOTAL (JD)", "TOTAL (PP)") then do;
     
length varname $ 32;
     
drop varname;
      varname = upcase( vname( xchar{i} ) );

     
if findw(dropList, varname, ",", "T") = 0 then do;
        dropList = catx(
",", dropList, varname);
      end;
   
end;
 
end;

 
* replace , by blank and write list into macro var ;
 
if last = 1 then do;
    dropList = translate(dropList,
" ", ",");
    putlog dropList=;
    call symputx("dropList", dropList);
  end;
run;

* create new DS and drop unwanted vars ;
data want;
  set have;
  drop &dropList;
run;
Super User
Posts: 10,028

Re: A Data Manupulation Issue _ Thank you

Posted in reply to need_sas_help

How about this one :


data have;
  infile cards dlm=","; 
  input
    subject : $12.
    F1 : $12.
    F2 : $12.
    F3 : $12.
    F4 : $12.
    F5 : $12.
    F6 : $12.
  ;
  cards; 
10002,     4,    7,   9,    9,               10,    9
10001,     0,    3,   4,   Total (JD),        7,    Total (PP)
10006,     8,    8,   8,    0,                9,    9
10007,     8,    8,   8,    Total (JD),       9,    9
10008,     8,    8,   8,    0,                Total (PP),  9
; 
run;

proc sql noprint;
 select cat('sum(',strip(name),'="Total (JD)" or ',strip(name),'="Total (PP)") as ',strip(name)) into : list separated by ','
  from dictionary.columns 
   where libname='WORK' and memname='HAVE' and upcase(name) like 'F%';

create table temp as
 select &list from have;
quit;
data _null_;
 set temp;
 length drop $ 200;
 array x{*} _numeric_;
 do i=1 to dim(x);
  if x{i} ne 0 then drop=catx(' ',drop,vname(x{i}));
 end;   
call symputx('drop',drop);
run;
data want;
 set have(drop= &drop );
run;

Xia Keshan

Ask a Question
Discussion stats
  • 2 replies
  • 189 views
  • 0 likes
  • 3 in conversation