BookmarkSubscribeRSS Feed
shahrushabh2710
Fluorite | Level 6

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.

5 REPLIES 5
shahrushabh2710
Fluorite | Level 6

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
Reeza
Super User

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;
shahrushabh2710
Fluorite | Level 6

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

Reeza
Super User

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.

shahrushabh2710
Fluorite | Level 6

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

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
  • 2297 views
  • 0 likes
  • 2 in conversation