DATA Step, Macro, Functions and more

How do i read (data,column names,datatype & size) from sas7bdat file and export it to an excel file

Reply
Occasional Contributor
Posts: 5

How do i read (data,column names,datatype & size) from sas7bdat file and export it to an excel file

I am new to SAS.

 

How do i read (data,column names,datatype & size) from sas7bdat file and export it to an excel file

A sheet having all the data and second sheet having all the columns name , datatype of that column and size of that column.

Occasional Contributor
Posts: 5

Re: How do i read (data,column names,datatype & size) from sas7bdat file and export it to an exc

I have this working 

 

/* reading a SAS File */

data new;
set '/folders/myfolders/samples/sales.sas7bdat';
run;

 

/* printing the data from a SAS File */
proc
print data='/folders/myfolders/samples/sales.sas7bdat';
run;

 

/* exporting a SAS File (all data) to excel */

proc export
data=new
dbms=xlsx
outfile="/folders/myfolders/all_excel/sales.sas7bdat.xlsx"
replace; sheet="all_data";
run;

 

what i want is something like attached image


Capture.PNG
Super User
Posts: 17,819

Re: How do i read (data,column names,datatype & size) from sas7bdat file and export it to an exc

Your code is a bit convoluted. I'm posting a cleaned up version that's a bit more clear, as well as using proc contents to create your metadata file. The number of rows would be the same for all variables so that isn't a helpful field. 

libname sample '/folders/myfolders/samples/';

data new;
set sample.sales;
run;

proc print data=new;
run;

/*Export code was fine*/

proc contents data=new;
run;

ods excel file='/folders/myfolders/samples/new_metadata.xlsx' style=meadow;

proc contents data=new;
run;

ods excel close;
Occasional Contributor
Posts: 5

Re: How do i read (data,column names,datatype & size) from sas7bdat file and export it to an exc

works great, thanks you.

 

Although i just want 3 sheets in an excel file one named "All Data" that has all the data, second one with name "Some Data" that has only the selected columns passed in query (example would be appreciated) and the third sheet with name as schema that has only the column name type and size.

 

proc
contents data=new;
run;

 

works awesome but gives a whole lot of data, i just want column name type and size and i just want in the order they are not sorted alphabetically.

 

Thanks in advance

 

PS: The exact excel file that i want is attached

Super User
Posts: 17,819

Re: How do i read (data,column names,datatype & size) from sas7bdat file and export it to an exc

If you want to use ODS Excel you can achieve this by nesting the following between the ODS Excel/ODS Excel Close; code.

 

 

1. Running a proc print of your data first

2. Running a proc print, including only your selection - use the VAR statement to filter out variables for example.

3. Running a proc contents but only selecting the output. You can look into the options in proc contents to restrict the output.

Occasional Contributor
Posts: 5

Re: How do i read (data,column names,datatype & size) from sas7bdat file and export it to an exc

not sure if i understand, can you explain with code ?

Ask a Question
Discussion stats
  • 5 replies
  • 448 views
  • 0 likes
  • 2 in conversation