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

Kayla_Tan222 — Mon, 10 Jun 2019 07:10:45 GMT

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

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

ChrisNZ — Mon, 10 Jun 2019 09:23:59 GMT

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

firstobs=2

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

Tom — Mon, 10 Jun 2019 12:25:59 GMT

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;

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

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

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

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