DATA Step, Macro, Functions and more

how to read in file with number of columns that is different every time?

Reply
Frequent Contributor
Posts: 133

how to read in file with number of columns that is different every time?

I want to write SAS code that can read in csv file every time it is read, number of columns maybe different.

In addition, I need to use different variables to look up different values.

let's say first time I have 3 columns, it's a look up table, use var1 - var2 to look up RF01

next time when I read in the file, it has 5 columns, use var1 - var3 to look up RF01 and RF02

So, given this situation, the column number is not fixed, and also look up column value is different too.

Any ideas?

Frequent Contributor
Posts: 139

how to read in file with number of columns that is different every time?

One Clarification.Var1-Var2 are two variables whcihis being used to lookup RF01. when file has 5 columns , we need to use four vairbales to look up.

Any example to explain this would be helpful

Frequent Contributor
Posts: 133

how to read in file with number of columns that is different every time?

1st time, table is like this:

var1 var2      RF01

us     fin       IR

eu     tech    FX

ca     svc     sprd

2nd time, table is like this

var1 var2   var3    RF01   RF02

us    fin     swp    crv01    FX

eu    corp  sptn   crv02    sprd

gb     bnk  edotn  crv04   ir

Super User
Posts: 10,020

Re: how to read in file with number of columns that is different every time?

Why not use proc import to import csv file.

but about your querying. I don't know which criterion you used to judge variable should be var: or RF: ?

Ksharp

Super User
Posts: 11,343

Re: how to read in file with number of columns that is different every time?

Will different iterations of this process also add any rows to your data?

I think the PROC IMPORT solution Ksharp mentioned is a good start but if you have rows added to your data there may be an issue with a variable column changing data types from numeric to string based on the content.

Suppose the first time you read the data with column 10 the values are numeric. Then in a later version added rows in column 10 now contain some text values. Proc import is likely to make that variable string.

Ask a Question
Discussion stats
  • 4 replies
  • 299 views
  • 0 likes
  • 4 in conversation