Error concatenating multiple data file

[Blandine]

Hello I have tried differents way of concatenating my file ID1992 ID1993.....ID2019 into one filr but I am having issue. I just test out 2 file but it's not working

1st method using append my code. I used force because sas recommended it when I ran without force. Also I have tried restarting SAS but it still doesn't work

proc Append
base=IDAHO.Id1992 data=IDAHO.Id1993; force;
run;

log error

proc Append
38 base=IDAHO.Id1992 data=IDAHO.Id1993; force;
-----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
39 run;

NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

2nd method using data set

/*Combining all the data file into one file*/
data IDAHO.Id1992;
set IDAHO.Id1993
IDAHO.Id1994;
run;

log error

/*Combining all the data file into one file*/
41 data IDAHO.Id1992;
42 set IDAHO.Id1993
43 IDAHO.Id1994;
ERROR: Variable STRUCTURE_NUMBER_008 has been defined as both character and numeric.
44 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set IDAHO.ID1992 may be incomplete. When this step was stopped there were 0
observations and 16 variables.
WARNING: Data set IDAHO.ID1992 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

thanks for you help

[Reeza]

Your variable types must match. So take a look at the variable structure number in your data sets and convert one.

Converting to character is likely the easiest but you'll have to rename it.

data want;
set have (rename = structure_number_008 = sn8);
structure_number_008 = put(sn8, 8.);
drop sn8.;
run;

Then merge usin gthe 'want' data set.

But, given your code I'm guessing you're reading in multiple files from somewhere using PROC IMPORT (hopefully not Excel files) and then trying to append them together. If the files are text or CSV you can fix this by reading all the files using a single data step instead and I'd highly recommend that approach instead.

[Kurt_Bremser]

The FORCE is an option of the PROC APPEND statement, so there must NOT be a semicolon before.

No matter what, you need to fix this first:

ERROR: Variable STRUCTURE_NUMBER_008 has been defined as both character and numeric.

Make sure that all datasets you want to concatenate have identical structure. Or at least the same attributes for those columns they have in common.

[Blandine]

Unfortunately, I can't fix that I am working with bridge data from 1992 till 2019 and they change the name of bridges during year so some have numeric and letter in their name others don't.It will be very difficult to change the name as it's at least 1000 bridges per years

[Kurt_Bremser]

So you convert all to character; numbers can easily be converted to strings.

[Blandine]

So I guess I can try to convert all into character,concatenate and bring in back to the original name? Is it possible? I don't want to loose Bridge name as I will use it later on.

[Kurt_Bremser]

So what is contained in STRUCTURE_NUMBER_008 in the 1993 and 1994 datasets?

From where do you get those datasets? If they originate from external sources, how did you get the data (file format), and how did you import it into SAS?

[Blandine]

STRUCTURE_NUMBER_008 is the name for a specific bridge, for example, my 1992 & 1994 file has this name "10300"  all numeric for the bridge

my 1993 has these name format  "00000000000H311" and "000000000010300". these are just example of bridge name. At this point, I am not sure it's the same bridge.

I got the data from the federal Highway website and it was a txt file that I imported.

I use a macro  because I was importing multiple files at once

%MACRO IMPORT_TXT(FILENAME=ID,OUTFILENAME=ID);
proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task 8_Back-casting\NBI Data from Fhwa site\Data with inspection year\ID_All years\&FILENAME..txt"
dbms=dlm
out=&OUTFILENAME (keep = STRUCTURE_NUMBER_008 MAINTENANCE_021 OWNER_022 YEAR_BUILT_027 ADT_029 SERVICE_ON_042A SERVICE_UND_042B STRUCTURE_KIND_043A STRUCTURE_TYPE_043B DECK_STRUCTURE_TYPE_107 DECK_COND_058 SUPERSTRUCTURE_COND_059 SUBSTRUCTURE_COND_060 PERCENT_ADT_TRUCK_109 SCOUR_CRITICAL_113 Inspection_year) replace;
delimiter=',';
Getnames=Yes;
guessingrows=100000;
run;
%MEND;

/* Calling Macro to import First file */
%IMPORT_TXT(FILENAME=ID1992,OUTFILENAME=IDAHO.ID1992);
/* Calling Macro to import second and n file */
%IMPORT_TXT(FILENAME=ID1993,OUTFILENAME=IDAHO.ID1993);

[Reeza]

Read the files with a data step, not proc import and you won't get any issues.

[Kurt_Bremser]

Doesn't the website provide a description of the file structure?

[Reeza]

Yes, it's here:
https://www.fhwa.dot.gov/bridge/nbi/format.cfm

[Kurt_Bremser]

That's good documentation, and it should be sufficient to write the data step.

I note that all the dates seem to be in fact years (numeric, 4 digits).

[Reeza]

If I could point you towards this post (selfishly)
https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

I would read one file in, take the input code from the log, verify it, and then use the method above.