How to: set an excel cell as a variable and reuse it

[mkimmi]

Hi everyone! So my question is multifold, but just some background: I'm trying to import a bunch of excel data for a decade of all 50 states, rearrange them, and ultimately merge them together

All excel sheets are in same format. Each time I import a sheet to run the code, I want to:

1. The state name is in the same Excel box for every sheet. I would like to retrieve the data in this box, hold it (probably as a variable "state"), then rewrite it as "State1" -- something generic so I can apply the code generically 

2. Could I then take the data stored as "state" within the code, to replace into something else. For instance at the end of the code I have SAS spit out a file which name includes the state's name, could I use this stored variable to label the file instead of manually changing the title every run?

3. Might be asking for too much, but is there a way to code so that my code of importing, rearranging data, etc., so that SAS just takes one file after another (+1 somewhere?) so I don't have to manually adjust the sheet name in the import section?

[mkimmi]

Or an alternative to 2) how could I retrieve the import file name to use to name the new file I want to spit out?

[Reeza]

Import that cell only using the RANGE option within PROC IMPORT and store the variable in a macro variable. Then you can use the macro variable with the same reference (&STATE1) wherever needed in your code. You do need to read the cell above and the value unfortunately as SAS does typically expect a header column. There may be ways around that but I'd get it working first.

Untested, rough idea code.

proc import out=stateName file=... dbms=exce; range="Sheet1$A1:A2"; run;

data _null_;
set stateName;
call symputx("StateName", varValue, 'g');
run;

%put &stateName;

[Reeza]

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[mkimmi]

Thanks! Seems helpful.

So actually would it be possible to replace a specific cell in Excel (always the same one) with "State" when I import it? In the excel, that cell is a state name ("Missouri" "Alaska" etc.,) and always automatically becomes a variable. I want to just replace it with "State" to run the code

[Tom]

Is the excel sheet organized as a dataset or not? 

Does the first row contain column headers that can by used as variable names?
Does the actual data start in the second row?
Do all of the cells in any given column contain the same type of data?

 Or is it just a seemingly random scattering data ?

[mkimmi]

I guess I should specify that I don't need to hold the name of the state anymore. 

Each excel when I import the dataset is always the same. I just want to be able to rename the first column, which is labeled by state name as the variable, as "State" regardless of what its name is when imported. I want to figure out how to rename variables by column position (came across arrays--didn't work well ultimately, and a macro--but unsure if I used it correctly)

This way I wouldn't have to edit the code manually: e.g.

rename Alabama = State

And change Alabama --> another state name every time.

[Tom · Accepted solution]

So that is an easier thing to understand and create code for.  We can use any dataset as the example to demonstrate what to do. Let's use SASHELP.CLASS.  So just query the metadata of the dataset and find the name of the first variable.  You can use DICTIONARY.COLUMNS (or the view SASHELP.VCOLUMN) or PROC CONTENTS output.  The variable VARNUM will let you know which is the first variable.

data have;
  set sashelp.class;
run;
proc sql noprint;
select nliteral(name) into :oldname trimmed 
from dictionary.columns
where libname='WORK' and memname='HAVE' and varnum=1
;
quit;

data want;
  set have (rename=(&oldname=STATE));
run;

proc print;
run;

[mkimmi]

Thank you! I wanted to ask if there is a particular source you'd recommend reading up on this esp dictionary.columns ---I've noticed people use it and tried to manipulate it on my own (unsuccessfully), until I used your code it finally worked

Thanks again

[Tom]

I usually just search

https://www.google.com/search?q=%40sas.com+dictionary+views

A couple of useful things I didn't notice when browsing those links:

• Fields like LIBNAME and MEMNAME that are the table keys are always stored as uppercase. 
• The SASHELP.Vxxxx shortcuts cannot pass thru your WHERE clauses.  So if you have a lot of open librefs it is better use SQL and query the DICTIONARY.xxxx tables directly instead so that it only tries to gather metadata on the librefs and members you are actually interested in.

[mkimmi]

Hi sorry! I'm back because have an question about proc sql when done in a loop.
I've set a macro to do this sql ^.
I have another code that calls on this macro for every file in a folder.
However, the only issue I'm running into now is that :oldname / &oldname is always retained from the first file. I thought it would be rewritten for every new file that I call onto to do this sql function...
essentially, first file has New York, &oldname = New York --- does the code is great: renames to State
second file has Alabama, but when proc sql &oldname is still New York, so ofc Alabama doesn't equal New York, so it cannot be renamed to State

Sometimes, I get this: WARNING: Apparent symbolic reference OLDNAME not resolved.
I tried looking up how to clear the dictionary tables so to speak at the end of the code so that when it loops I wouldn't have this issue, but I've only found that I may have to delete macro at the end to do so---which then wouldn't allow me to loop a function since I would have no macro to call on lol

When I manually apply the code to each file separately, it works fine. Not sure what's wrong?

[Tom]

Sounds like you are not setting the macro variable. 

Would need to see the actual code you used, but if you are trying to use CALL EXECUTE to call a macro once for each observation in a dataset with a list of datasets then make sure to use %NRSTR() around the macro call to prevent timing issue.

So instead of doing something like:

data _null_;
  set filelist;
  call execute('%mymacro('||dataset||')');
run;

Add %NRSTR() like this:

data _null_;
  set filelist;
  call execute('%nrstr(%mymacro)('||dataset||')');
run;

 Plus your SAS log will much easier to read:

+  %mymacro(dataset1)
+  %mymacro(dataset2)
...