topic How to get data from e.g. an Excel file into a vector in SAS Programming

How to get data from e.g. an Excel file into a vector

BjarneGjerde — Thu, 15 Mar 2018 07:55:55 GMT

How to get these example data from e.g. an Excel file (or ascii file):

Var c1 c2

A 2 5

B 4 3

C 6 7

Into the vectors c1 and c2 in the below program.

Proc optmodel;

set box={"A","B","C"};

var Q{i in box} >=0;

number c1{i in box}=[2 4 6];

number c2{i in box}=[5 3 7];

etc.

quit;

Re: How to get data from e.g. an Excel file into a vector

Ksharp — Thu, 15 Mar 2018 12:49:58 GMT

Calling @RobPratt

Re: How to get data from e.g. an Excel file into a vector

RobPratt — Thu, 15 Mar 2018 13:43:13 GMT

First convert to a SAS data set, say indata, by using PROC IMPORT or a DATA _null_ step. Then use the READ DATA statement:

Proc optmodel;
   set <str> box;
   var Q{box} >=0;
   number c1{box};
   number c2{box};
   read data indata into box=[Var] c1 c2;
   print c1 c2;
quit;

PROC OPTMODEL questions will get more immediate response in the Mathematical Optimization, Discrete-Event Simulation, and OR community.

Re: How to get data from e.g. an Excel file into a vector

BjarneGjerde — Thu, 15 Mar 2018 14:54:06 GMT

Thanks a lot!

But I do not see how to get each line or Product (Var A, B, C) – into e.g. c1 {i in box}.

Proc optmodel;

set box={"A","B","C"};

var Q{i in box} >=0;

number c1{i in box}=[2 4 6];

number c2{i in box}=[5 3 7];

Re: How to get data from e.g. an Excel file into a vector

RobPratt — Sat, 17 Mar 2018 13:27:30 GMT

The READ DATA statement populates box, c1, and c2 for you. That way, you can solve a different instance of the same problem just by changing data sets without having to change your PROC OPTMODEL code.