import weird variables

[epidemiologystu]

Hello,

I'm looking to import two excel files. Use proc transpose to the datasheet called "profilers" so that the CASRN is a column. Then I need to merge the datasets "empirical" &  "profilers" by the CAS #. 

Currently having issues importing both excel files. I have tried using CARDS instead and the log indicates the issue importing begins at the very first CAS RN.

Note that the CAS RN has a format anywhere from:

XXX-XX-X to XXXXXX-XX-X

Also note that the datasheet "profilers" has the CAS RN duplicated at times, and this is meant to be maintained.

[ballardw]

Show your code and CARDS statements. Best is to also include the log from attempting the code (the actual cards won't appear in the Log, so show that separately). Copy the log, open a text box on the forum and paste the log along with any notes or messages generated by the code.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

[Patrick]

@epidemiologystu In the future please provide more detail like your code and SAS log.

Below works in my environment.

%let empi_xlsx=c:\temp\empirical.xlsx;
%let prof_xlsx=c:\temp\profilers.xlsx;


/** read Excels **/
proc import 
  out=work.empirical_raw
  datafile="&empi_xlsx"
  dbms=excel
  replace
  ;
  getnames=yes;
run;

proc import 
  out=work.profilers_raw
  datafile="&prof_xlsx"
  dbms=excel
  replace
  ;
  getnames=no;
run;

/** post processing **/

/* empirical: remove rows with missing group */ 
data empirical;
  set empirical_raw;
  if not missing(group);
run;


/* profilers: drop columns that are all missing */

/* from: https://communities.sas.com/t5/SAS-Programming/how-to-take-all-missing-variables-list-in-dataset/td-p/530323 */
ods select none;
ods output nlevels=_counts;
proc freq data=profilers_raw nlevels;
  table _all_;
run;
ods select all;
%let drop_list=;
proc sql noprint;
  select TableVar into :drop_list separated by ' '
  from _counts
  where NNonMissLevels=0
  ;
  drop table _counts;
quit;

data profilers;
  set profilers_raw;
  drop &drop_list;
run;