how to import data from excel csv (the column order is not same) without mess up the column in sas

[Kayla_Tan222]

Hi all,

I have a lot of excel csv files that have same columns name with different data, however some of the file have same column name also but in different order. This cause the data is messed when I import into sas. For Ex,

original file 1

fruits                 vegetable                  number               date

orange                  tomato                     5                       10/6/2019

apple                     corn                       10                       5/5/2019

original file 2  

number                      date                   fruits                 vegetable

6                            1/1/2019               watermelon          cabbage 

8                             22/2/1997              kiwi                    carrot    

original file 3

date                     number                    vegetable           fruits

8/8/2016                 44                        asparagus             durian

7/4/2006                 99                           eggplant             banana

the column order is not the same for almost every csv file.

if I put my code like below, the data from original file 2 and 3 does not follow the column name when import.

libname HAPPY "D:\school";

Data HAPPY.school;

%let _EFIERR_=0;

infile "D:\school\file\fruitandvegelist.csv" dlm=',' MISSOVER DSD lrecl=32767 firstobs=2;

　

informat fruits $4.;

informat vegetable $4.;

informat number 4.;

informat date yymmdd10.;

format fruits $4.;

format vegetable $4.;

format number 4.;

format date yymmdd10.;

input

fruits $

vegetable $

number

date;

run;

the data for orginal file 2 will import the number as fruits column, date as vegetable column and so on.

it will follow the order in the code I type.

How can I solve this problem? I want the data automatically follow the column name

[ChrisNZ]

Why do you skip the column names by using this option?

firstobs=2

[Tom]

Read the header row to tell what variables are in which column.  You can put that list into a macro variable and then use that macro variable to generate the INPUT statement to read the variables in the right order.

libname HAPPY "D:\school";
filename CSV "D:\school\file\fruitandvegelist.csv";

data _null_;
  infile csv obs=1;
  input;
  call symputx('varlist',translate(_infile_,' ',','));
run;


data HAPPY.school;
  infile  CSV dsd truncover firstobs=2;
  length fruits $4 vegetable $4 number 8 date 8;
  informat date yymmdd10.;
  format date yymmdd10.;
  input &varlist;
run;