BookmarkSubscribeRSS Feed
R_Win
Calcite | Level 5
Hi,
How to import descriptor portion (Variables names ) from Excel and the corresponding data from text file .

Regards
Sam
5 REPLIES 5
DanielSantos
Barite | Level 11
Sam.

Please be more specific.

What are you trying to do? Where is your data?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
R_Win
Calcite | Level 5
Hi

I am having variables in Excel sheet and the dataset was created and i have the data in txt file now i want to create the dataset with variables in excel and data in csv.

Excel Variable:
name age


Data in Text.
sas,23
bi,45
Nani,90
kool,89

output should be

name age
sas 23
bi 45
Nani 90
kool 89
DanielSantos
Barite | Level 11
OK.

Then keeping it simple, I would go as Linus suggested.

PROC IMPORT form EXCEL (be sure to load the first row as an header row)

Then you could maybe load the table layout (var names) into a macro var list, say like this:

proc sql noprint;
select NAME into :VARNAMES separated by ',' from DICTIONARY.COLUMNS
where LIBNAME eq ' MEMNAME eq ''
order by NPOS;
quit;

After that, and assuming your data file is not extensive, build a second data file with an header row and the concatenated data.

data _null_;
infile '';
file '';
if _N_ eq 1 then put "&VARNAMES"; * put header row;
input; * read one row;
put _infile_; * write read buffer to second file;
run;

Then PROC IMPORT the resulting second file, loading the first row as an header row.

SAS will try to guess the size and type of each column from the first 20 rows (you can modify this number through the registry) in the file.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Peter_C
Rhodochrosite | Level 12
on a recent project, we got a bit - beyond simple - a lot more specific
In the excel sheet metadata was maintained, providing name and type - as defined by storage length and/or informat. An optional format could be defined. The expected column header was also defined.
The order of metadata was the output column order.
A match between column header and "expected column header" was made. Unexpected columns would be "read" as $20.
It was designed to be an iterative process to support new data. It was built because the delivered information had "creep" as the use of underlying data columns adapted and new columns were added.
It overcame a few problems in the original "excel data collection".
1 mixed type columns
2 special missing values ( cells with symbols allied to special conditions instead of numbers)
3 columns delivered don't always match the columns required list.
The data was received as a formated excel sheet. It was "unformatted" (all columns given "format=text"). Then the data were saved from excel as a plain csv file. It is this csv file which is loaded subject to the rules of the metadata.
While the metadata can be maintained in a "quality" that is easy and reliable for SAS to read from excel (with just a libname statement making the connection), the primary data received, were not able to be controlled in such a reliable way. Hence the elaborate import management.

Of course once we thought we had a reliable method for handling almost any data supplied, new complexities demonstrated its shortcomings: sometimes column headers are not unique ("which metadata row should we choose?"); data sometimes needed more than one row of column header to define it.
Basically, these problems are caused by data that does not conform to "standard csv" but that is the nature of "data in excel".
At least "excel data" never causes a "problem of volume" 😉

PeterC
LinusH
Tourmaline | Level 20
Well, yes.
Use PROC IMPORT on your Excel-file, and then read your text-file with appropriate method,
and PROC APPEND to table created by PROC IMPORT.
/Linus
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1226 views
  • 0 likes
  • 4 in conversation