How do I import text file without column names into SAS format SAS Viya?

Question

I have a text file uploaded in workbench in SAS Viya without column headers. I need covert the file into SAS format and assign the column name myself which I have in word document. I get this code when I use the automated import function in SAS Viya but I don't know how to assign column names. Any help would be great!

proc sql;
%if %sysfunc(exist(WORK.NEWDATA)) %then %do;
    drop table WORK.NEWDATA;
%end;
%if %sysfunc(exist(WORK.NEWDATA,VIEW)) %then %do;
    drop view WORK.NEWDATA;
%end;
quit;



FILENAME LOC DISK '/workspace/workbench/myorg/data/home/olddatatoimport.txt';

PROC IMPORT DATAFILE=LOC
    DBMS=DLM
    OUT=WORK.NEWDATA;
    GETNAMES=YES;
RUN;

Show the structure of the data in the Word document. Are you able to read the document with SAS ? Does the Word document change often or is this a one-time affair ? — Richard
the data file is in text file format, the header names are in separate word document. I have the word document in different location. When I just use the import function in SAS viya (the code I provided) it just assings Var1 Var2 etc. But I would need the names to be Var1=State, Var2=City — FunT
Does the WORD document also describe what is in the variables (character strings, numbers, dates, times, datetimes)? If so then skip forcing SAS to guess how to define the variables by using PROC IMPORT and instead just write a data step to read the file directly. — Tom

Richard Richard · Accepted Answer · 2020-10-19T22:02:28

A RENAME statement can be used to change the default column names assigned by PROC IMPORT when GETNAMES=NO.

Example:

Presume the column names in the Word document were copy and pasted into SAS datalines

filename onlydata temp;

* create some sample data;

data _null_;
  file onlydata dlm=',';
  set sashelp.class(obs=3);
  put name age sex height weight;
run;

* log the sample data just to get a look-see;

data _null_;
  infile onlydata;
  input;
  put _infile_;
run;

* IMPORT the data only datafile, no names to get!;
* default column names are VAR1 to VAR<n>;

proc import file=onlydata replace out=onlydata dbms=csv;
  GETNAMES=NO;
run;

* presume the column names in the Word document are in the correct order
* (matching the data file) and were copy & pasted into the datalines;

data column_names(label="From WORD document");
  length name $32;
  input name;
datalines;
name
age 
sex 
height
weight
run;

* Construct the oldname=newname source code pairs for a RENAME statement;
data _null_;
  set column_names end=last_column;

  length pairs $32000;
  retain pairs;

  pairs = catx(' ', pairs, catx('=', cats('var',_n_), name));

  if last_column then 
    call symput ('pairs', trim(pairs));
run;

* use Proc DATASET to modify the header portion of the datas set;
* will not rewrite the entire data set;

proc datasets nolist lib=work;
  modify onlydata;
  rename &pairs;
run;
quit;

%let syslast = onlydata;

log image of the look-see

data set with default PROC IMPORT column names

data set after RENAME statement applied via PROC DATASETS

How do I import text file without column names into SAS format SAS Viya?

1 Answers