topic Re: Validate column names and fetch data into macro variables in SAS Programming

Validate column names and fetch data into macro variables

Aditi24 — Mon, 12 Jun 2017 12:12:13 GMT

Hi,

I have imported data from excel to a sas-data set. The dataset after exporting looks like this.

Name| ID | Salary | Designation

------------------------------------------------

ABC | 11 | 2000$ | Manager

The table will only have 1 column .

Now I want to validate the column label if an if condition and store the value of column in a macro variable:-

pseudo code:-

if macro_label="Name"
then do;
call symput(symget('myMVar'),Name);
end;

Please suggest a way to validate the column labels of a sas dataset.

Re: Validate column names and fetch data into macro variables

Kurt_Bremser — Mon, 12 Jun 2017 09:54:24 GMT

You cannot export data from Excel to a SAS dataset; Excel cannot write SAS datasets.

So please describe exactly what you have done to get the data into SAS (which file format was written from Excel, and which step was used in SAS for import)

Re: Validate column names and fetch data into macro variables

RW9 — Mon, 12 Jun 2017 10:27:41 GMT

If you have have imported the Excel file into SAS then the metadata will be present in the metadata tables. For instance, if I have a dataset ABC in WORK, then:

data _null_;
  set sashelp.vcolumns (where=(libname="WORK" and memname="ABC" and name="COL1"));
  call symputx('myVar',name);
run;

Would work. However, why put a column label in a macro variable. It is already present in the metadata tables, so when you want to use it, pull the information from there. Otherwise you may hit several road blocks (invalida characters, line lengths for multiples, multiple values etc.). If you can elaborate on what you are doing we can look.

Re: Validate column names and fetch data into macro variables

PaigeMiller — Mon, 12 Jun 2017 12:30:56 GMT

Hello, @Aditi24, I'm really having trouble understanding your problem. Could you give specific examples of what macro variables you want to create from this input data, and what values they should have?

Hello, @RW9, you ask "However, why put a column label in a macro variable." Because that's one legitimate use of macro variables, to contain and make use of text that might vary from one execution of the program to another.

Re: Validate column names and fetch data into macro variables

RW9 — Mon, 12 Jun 2017 12:39:21 GMT

Please do stop spamming my posts where I ask simple questions. The reason I asked the question is clearly stated in the text:

"However, why put a column label in a macro variable. It is already present in the metadata tables, so when you want to use it, pull the information from there."

Of which you have only quoted part. In most scenarios it would be quicker and simpler to refer to an existing dataset which contains the required information than to create a whole set of coding to emulate that which is already freely available. If there are specific set of circumstances where this is needed, then other points have to be raised about sepcial characters when de-referencing etc.

Re: Validate column names and fetch data into macro variables

PaigeMiller — Mon, 12 Jun 2017 12:57:38 GMT

Ok, RW9, I'll quote the whole thing next time. I still state that putting text into a macro variable for use in a program is a perfectly legitimate thing to do.