Combining datasets from directory issue

[Q1983]

data have1_20200207;

infile datalines;

input Invalid $ Dept1N Dept2N ;

return;

datalines;

! 203382 1560000

! 850000 1650000

;

run;

data have2_20200206;

infile datalines;

input Invalid $ Dept1N Dept2N ;

return;

datalines;

! 651144 1560000

! 254411 1650000

;

run;

data combine;

set have :; /*combine the datasets into one*/

run;

The above code works. I am able to stack the 2 datasets into one

Here is the actual scenario:

I am doing a proc import from a directory as follows:

libname roll "/myshare/do/sascode/reporting/mps_roll";

1.PROC IMPORT out=roll.sm_workitems_&rpt_date(keep= ln_no Worklist report_date)

datafile="/my_share/Suspense/sm_workitems_&rpt_date..csv"

   dbms=csv replace;

run;

The rpt_date is a date extention at the end(example)

SM_WORKITEMS_20200205

These are individual datasets by date.

2. data sm_items;

set roll.sm_workitems_&rpt_date;

run; I do this because I need to add the report_date in step3

3. data roll.sm_workitems_&rpt_date;

set sm_items;

report_date="&CurrDate."d;

format report_date date9.;

run;

4. data pop_tot(keep=ln_no Worklist report_date); /*stack the datasets in one*/
   set roll.sm_workitems: ;

run;

I now want to stack the datasets into one dataset just as I did above. Here is the issue.  Despite using a keep= function I get the following error below because the original location where I did the first import has a field called  !.  I do not understand why sas is referencing the field when I asked that it not be included. Essentially it ignores the keep command.  My question is do dataset act differently and referencing all fields if they originated from a share drive in .csv??  When I try

options validvarname=any;  it still generates the error

ERROR: The value ! is not a valid SAS name.

ERROR: The value ! is not a valid SAS name.

[ballardw]

I don't see any code where you "ask not include" the field. Or what it's actual name is. typically SAS would IMPORT a field named ! as _ .

Please run proc contents on both of the data sets you are attempting to combine so we can tell the actual name and type for the variables.

The way you are using KEEP it is on the OUTPUT after the sets are read into the data vector. You may need to add the KEEP as data set options (which unfortunately means the list : isn't going to work

data pop_tot; /*stack the datasets in one*/
    set roll.sm_workitems_set1  (keep=ln_no Worklist report_date)
          roll.sm_workitems_set2  (keep=ln_no Worklist report_date)
;

Also you may need to examine the settings of the systemoption VALIDVARNAME when importing and when attempting to merge them. SAS won't allow the use of a variable name like '!' unless VALIDVARNAME=ANY is in effect. In which case you have to use '!'n , for a name literal, to reference the field. You can check with:

proc options option=validvarname;
run;

And you will see in the log something like:

 VALIDVARNAME=V7   Specifies the rules for valid SAS variable names that can be created and
                   processed during a SAS session.

If V7 is in effect then SAS won't want to process any non-standard names such as you say you have.

BTW using multiple Proc Import to read in similarly structured CSV files is poor practice. Using a data step to read them means that YOU control the names of the variables, the variable type and length. It is very easy to use the data step code generated by Proc Import one type to get the base data step code and modify as needed, such as setting desired maximum expected lengths of character variables. Save the code then change the infile statement to point to one file and the output data set name for the output.

[Tom]

It would help to show the text from the log where the error occurs. Make sure to use the Insert Code button in the forum editor to get a pop-up to paste the text. That way to spacing is preserved.  That way it will be clearer what SAS is actually complaining about.

You cannot normally have a variable with an exclamation point in its name. To use names like that you need to set the VALIDVARNAME option to ANY.

So perhaps when you created the dataset you have the VALIDVARNAME=ANY option set and now that you are trying to read the dataset you have set it back VALIDVARNAME=V7.   So even though perhaps you only want to keep some specific variables SAS is mad because the dataset you are reading has other variables that have names it doesn't like.