topic Re: A Data Manupulation Issue _ Thank you in SAS Procedures

A Data Manupulation Issue _ Thank you

need_sas_help — Tue, 21 Oct 2014 17:45:27 GMT

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.

Re: A Data Manupulation Issue _ Thank you

BrunoMueller — Wed, 22 Oct 2014 08:20:44 GMT

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;

Re: A Data Manupulation Issue _ Thank you

Ksharp — Wed, 22 Oct 2014 12:46:25 GMT

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