Solved: Re: Headers on first two rows of csv for export

Q1983 · Posted 01-28-2022 10:59 AM

data have;
set sashelp.electric;
run;



%let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,);
%let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,);

%put &header1;
%put &header2;
Data _Null_;
  Set have ;
  	FILE "C:/Have..csv" LINESIZE=8000 DLM="," dsd;
       IF _N_ EQ 1 THEN DO;
	   	PUT "&Header1";
	    PUT "&Header2";       
           HEADEROUT = 1;
               END;
           
RUN;

I am trying to insert a 2 row header on a csv file and then export it. It needs to be spaced and should look something like this. Is there a way to do this with a %let statement for the header as shown in my code

7	0						Data Needed				New Submission Data
Status						Data Needed					New Submission Data
Customer	Revenue	Year	RevTip	AllPower	AllTip	Coal	CoalTip	Nuclear	NukeTip	NaturalGas	GasTip	Hydro	HydroTip	Other	OtherTip
Commercial	$63	1994	title="Bar: Commercial Revenue: $63 (billion)"
Industrial	$48	1994	title="Bar: Industrial Revenue: $48 (billion)"
Other	$7	1994	title="Bar: Other Revenue: $7 (billion)"
Residential	$85	1994	title="Bar: Residential Revenue: $85 (billion)"	3,247,522	title="Line: All sources Year: 1994 Power: 3,247,522 GWh"	1,690,694	title="Line: Coal Year: 1994 Power: 1,690,694 GWh"	640,440	title="Line: Nuclear Year: 1994 Power: 640,440 GWh"	460,219	title="Line: Natural Gas Year: 1994 Power: 460,219 GWh"	260,126	title="Line: Hydropower Year: 1994 Power: 260,126 GWh"	196044	title="Line: Other sources Year: 1994 Power: 196,044 GWh"
Commercial	$66	1995	title="Bar: Commercial Revenue: $66 (billion)"
Industrial	$47	1995	title="Bar: Industrial Revenue: $47 (billion)"
Other	$7	1995	title="Bar: Other Revenue: $7 (billion)"
Residential	$88	1995	title="Bar: Residential Revenue: $88 (billion)"	3,353,487	title="Line: All sources Year: 1995 Power: 3,353,487 GWh"	1,709,426	title="Line: Coal Year: 1995 Power: 1,709,426 GWh"	673,402	title="Line: Nuclear Year: 1995 Power: 673,402 GWh"	496,058	title="Line: Natural Gas Year: 1995 Power: 496,058 GWh"	310,833	title="Line: Hydropower Year: 1995 Power: 310,833 GWh"	163768	title="Line: Other sources Year: 1995 Power: 163,768 GWh"
Commercial	$68	1996	title="Bar: Commercial Revenue: $68 (billion)"
Industrial	$48	1996	title="Bar: Industrial Revenue: $48 (billion)"
Other	$7	1996	title="Bar: Other Revenue: $7 (billion)"
Residential	$91	1996	title="Bar: Residential Revenue: $91 (billion)"	3,444,188	title="Line: All sources Year: 1996 Power: 3,444,188 GWh"	1,795,196	title="Line: Coal Year: 1996 Power: 1,795,196 GWh"	674,729	title="Line: Nuclear Year: 1996 Power: 674,729 GWh"	455,056	title="Line: Natural Gas Year: 1996 Power: 455,056 GWh"	347,162	title="Line: Hydropower Year: 1996 Power: 347,162 GWh"	172046	title="Line: Other sources Year: 1996 Power: 172,046 GWh"
Commercial	$70	1997	title="Bar: Commercial Revenue: $70 (billion)"
Industrial	$47	1997	title="Bar: Industrial Revenue: $47 (billion)"
Other	$7	1997	title="Bar: Other Revenue: $7 (billion)"
Residential	$91	1997	title="Bar: Residential Revenue: $91 (billion)"	3,492,172	title="Line: All sources Year: 1997 Power: 3,492,172 GWh"	1,845,016	title="Line: Coal Year: 1997 Power: 1,845,016 GWh"	628,644	title="Line: Nuclear Year: 1997 Power: 628,644 GWh"	479,399	title="Line: Natural Gas Year: 1997 Power: 479,399 GWh"	356,453	title="Line: Hydropower Year: 1997 Power: 356,453 GWh"	182661	title="Line: Other sources Year: 1997 Power: 182,661 GWh"
Commercial	$73	1998	title="Bar: Commercial Revenue: $73 (billion)"
Industrial	$47	1998	title="Bar: Industrial Revenue: $47 (billion)"
Other	$7	1998	title="Bar: Other Revenue: $7 (billion)"
Residential	$93	1998	title="Bar: Residential Revenue: $93 (billion)"	3,620,295	title="Line: All sources Year: 1998 Power: 3,620,295 GWh"	1,873,516	title="Line: Coal Year: 1998 Power: 1,873,516 GWh"	673,702	title="Line: Nuclear Year: 1998 Power: 673,702 GWh"	531,257	title="Line: Natural Gas Year: 1998 Power: 531,257 GWh"	323,336	title="Line: Hydropower Year: 1998 Power: 323,336 GWh"	218484	title="Line: Other sources Year: 1998 Power: 218,484 GWh"
Commercial	$73	1999	title="Bar: Commercial Revenue: $73 (billion)"
Industrial	$47	1999	title="Bar: Industrial Revenue: $47 (billion)"
Other	$7	1999	title="Bar: Other Revenue: $7 (billion)"
Residential	$93	1999	title="Bar: Residential Revenue: $93 (billion)"	3,694,810	title="Line: All sources Year: 1999 Power: 3,694,810 GWh"	1,881,087	title="Line: Coal Year: 1999 Power: 1,881,087 GWh"	728,254	title="Line: Nuclear Year: 1999 Power: 728,254 GWh"	556,396	title="Line: Natural Gas Year: 1999 Power: 556,396 GWh"	319,536	title="Line: Hydropower Year: 1999 Power: 319,536 GWh"	209537	title="Line: Other sources Year: 1999 Power: 209,537 GWh"
Commercial	$78	2000	title="Bar: Commercial Revenue: $78 (billion)"
Industrial	$49	2000	title="Bar: Industrial Revenue: $49 (billion)"
Other	$7	2000	title="Bar: Other Revenue: $7 (billion)"
Residential	$98	2000	title="Bar: Residential Revenue: $98 (billion)"	3,802,105	title="Line: All sources Year: 2000 Power: 3,802,105 GWh"	1,966,265	title="Line: Coal Year: 2000 Power: 1,966,265 GWh"	753,893	title="Line: Nuclear Year: 2000 Power: 753,893 GWh"	601,038	title="Line: Natural Gas Year: 2000 Power: 601,038 GWh"	275,573	title="Line: Hydropower Year: 2000 Power: 275,573 GWh"	205337	title="Line: Other sources Year: 2000 Power: 205,337 GWh"
Commercial	$86	2001	title="Bar: Commercial Revenue: $86 (billion)"
Industrial	$50	2001	title="Bar: Industrial Revenue: $50 (billion)"
Other	$8	2001	title="Bar: Other Revenue: $8 (billion)"
Residential	$103	2001	title="Bar: Residential Revenue: $103 (billion)"	3,736,644	title="Line: All sources Year: 2001 Power: 3,736,644 GWh"	1,903,956	title="Line: Coal Year: 2001 Power: 1,903,956 GWh"	768,826	title="Line: Nuclear Year: 2001 Power: 768,826 GWh"	639,129	title="Line: Natural Gas Year: 2001 Power: 639,129 GWh"	216,961	title="Line: Hydropower Year: 2001 Power: 216,961 GWh"	207771	title="Line: Other sources Year: 2001 Power: 207,771 GWh"
Commercial	$87	2002	title="Bar: Commercial Revenue: $87 (billion)"
Industrial	$48	2002	title="Bar: Industrial Revenue: $48 (billion)"
Other	$7	2002	title="Bar: Other Revenue: $7 (billion)"
Residential	$107	2002	title="Bar: Residential Revenue: $107 (billion)"	3,858,452	title="Line: All sources Year: 2002 Power: 3,858,452 GWh"	1,933,130	title="Line: Coal Year: 2002 Power: 1,933,130 GWh"	780,064	title="Line: Nuclear Year: 2002 Power: 780,064 GWh"	691,006	title="Line: Natural Gas Year: 2002 Power: 691,006 GWh"	264,329	title="Line: Hydropower Year: 2002 Power: 264,329 GWh"	189923	title="Line: Other sources Year: 2002 Power: 189,923 GWh"
Commercial	$96	2003	title="Bar: Commercial Revenue: $96 (billion)"
Industrial	$52	2003	title="Bar: Industrial Revenue: $52 (billion)"
Other	$1	2003	title="Bar: Other Revenue: $1 (billion)"
Residential	$111	2003	title="Bar: Residential Revenue: $111 (billion)"	3,883,185	title="Line: All sources Year: 2003 Power: 3,883,185 GWh"	1,973,737	title="Line: Coal Year: 2003 Power: 1,973,737 GWh"	763,733	title="Line: Nuclear Year: 2003 Power: 763,733 GWh"	649,908	title="Line: Natural Gas Year: 2003 Power: 649,908 GWh"	275,806	title="Line: Hydropower Year: 2003 Power: 275,806 GWh"	220002	title="Line: Other sources Year: 2003 Power: 220,002 GWh"
Commercial	$101	2004	title="Bar: Commercial Revenue: $101 (billion)"
Industrial	$53	2004	title="Bar: Industrial Revenue: $53 (billion)"
Other	$1	2004	title="Bar: Other Revenue: $1 (billion)"
Residential	$116	2004	title="Bar: Residential Revenue: $116 (billion)"	3,970,555	title="Line: All sources Year: 2004 Power: 3,970,555 GWh"	1,978,620	title="Line: Coal Year: 2004 Power: 1,978,620 GWh"	788,528	title="Line: Nuclear Year: 2004 Power: 788,528 GWh"	708,979	title="Line: Natural Gas Year: 2004 Power: 708,979 GWh"	268,417	title="Line: Hydropower Year: 2004 Power: 268,417 GWh"	226011	title="Line: Other sources Year: 2004 Power: 226,011 GWh"
Commercial	$111	2005	title="Bar: Commercial Revenue: $111 (billion)"
Industrial	$58	2005	title="Bar: Industrial Revenue: $58 (billion)"
Other	$1	2005	title="Bar: Other Revenue: $1 (billion)"
Residential	$128	2005	title="Bar: Residential Revenue: $128 (billion)"	4,054,688	title="Line: All sources Year: 2005 Power: 4,054,688 GWh"	2,013,179	title="Line: Coal Year: 2005 Power: 2,013,179 GWh"	781,986	title="Line: Nuclear Year: 2005 Power: 781,986 GWh"	757,974	title="Line: Natural Gas Year: 2005 Power: 757,974 GWh"	269,587	title="Line: Hydropower Year: 2005 Power: 269,587 GWh"	231962	title="Line: Other sources Year: 2005 Power: 231,962 GWh"

data_null__ · Posted 01-28-2022 11:35 AM

If I understand correctly you are missing the part that writes the obs from HAVE to the new file. I comments the line HEADER=1; and added put (_ALL_)(:);

proc sql noprint;
   select quote(strip(name)),varnum into :header3 separated by " ',' ", :dummy
      from dictionary.columns
      where (memname = 'HAVE' and libname = 'WORK')
      order varnum;
   quit;
%put NOTE: &=header3;

%let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,);
%let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,);

%put &header1;
%put &header2;
Data _Null_;
   Set have ;
   FILE "./Have.csv" dsd;
   IF _N_ EQ 1 THEN DO;
      PUT "&Header1";
      PUT  "&Header2";
      put  &header3;
      *HEADEROUT = 1;
      END;
   put (_all_)(:);
   RUN;

View solution in original post

data_null__ · Posted 01-28-2022 11:35 AM

If I understand correctly you are missing the part that writes the obs from HAVE to the new file. I comments the line HEADER=1; and added put (_ALL_)(:);

proc sql noprint;
   select quote(strip(name)),varnum into :header3 separated by " ',' ", :dummy
      from dictionary.columns
      where (memname = 'HAVE' and libname = 'WORK')
      order varnum;
   quit;
%put NOTE: &=header3;

%let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,);
%let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,);

%put &header1;
%put &header2;
Data _Null_;
   Set have ;
   FILE "./Have.csv" dsd;
   IF _N_ EQ 1 THEN DO;
      PUT "&Header1";
      PUT  "&Header2";
      put  &header3;
      *HEADEROUT = 1;
      END;
   put (_all_)(:);
   RUN;

Q1983 · Posted 01-28-2022 10:51 PM

Thanks this worked. I should have specified that the third row represents the column headers beginning with Customer, Revenue etc. I also want to show the column name underneath the two column header from the code you showed me. How can I do that portion???

Q1983 · Posted 01-29-2022 02:04 PM

Thanks this worked. I should have specified that the third row represents the column headers beginning with Customer, Revenue etc. I also want to show the column name underneath the two column header from the code you showed me. How can I do that portion???

Q1983 · Posted 01-29-2022 02:30 PM

Cancel the question I believe I have solution. Thanks

Q1983 · Posted 02-19-2022 12:52 PM

Thanks the solution worked. Here is my question. Using that same script you offered Is there a way to use an excel file extension (ie xlsb or xlsx) using those same headers?

proc sql noprint;
   select quote(strip(name)),varnum into :header3 separated by " ',' ", :dummy
      from dictionary.columns
      where (memname = 'HAVE' and libname = 'WORK')
      order varnum;
   quit;
%put NOTE: &=header3;

%let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,);
%let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,);

%put &header1;
%put &header2;
Data _Null_;
   Set have ;
   FILE "./Have.csv" dsd;
   IF _N_ EQ 1 THEN DO;
      PUT "&Header1";
      PUT  "&Header2";
      put  &header3;
      *HEADEROUT = 1;
      END;
   put (_all_)(:);
   RUN;

I tried using xlsb and xlsx and it generates a file extension error "cannot open"

Tom · Posted 02-19-2022 03:29 PM

An XLSX file is a binary file, you cannot write text to like you can a simple comma delimited text file.

Sounds like you just want to produce a REPORT, not an EXPORT.

So use PROC REPORT.

Perhaps something like this is what you want?

ods excel file='c:\downloads\class.xlsx'
  options (embedded_titles = 'yes')
;
title 'Report of SASHELP.CLASS';
proc report data=sashelp.class;
  column name sex ('measures' age height weight);
run;
ods excel close;

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away