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

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

shahrushabh2710 — Tue, 23 Feb 2016 19:35:59 GMT

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.

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

shahrushabh2710 — Tue, 23 Feb 2016 19:48:44 GMT

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

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

Reeza — Tue, 23 Feb 2016 20:00:07 GMT

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;

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

shahrushabh2710 — Tue, 23 Feb 2016 20:29:36 GMT

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

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

Reeza — Tue, 23 Feb 2016 20:38:48 GMT

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.

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

shahrushabh2710 — Tue, 23 Feb 2016 20:44:39 GMT

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