topic Re: Unmerging Excel cells in SAS in ODS and Base Reporting

Unmerging Excel cells in SAS

sarahsasuser — Tue, 14 Jan 2014 21:21:03 GMT

Hi All,

I have an excel file like the Have file shown below. There is a merged city field that I'd like to unmerge so that the city data shows on each line. However, sometimes that merged field will be applicable to 2 lines, 3 lines, or only 1 line. How do I get the Want file below?

Many Thanks,

Sarah

Have:

City	Building #	Specs
San Francisco, CA	121	A
	154	BB
	225	DC
Athens, GA	2568	AA
Chicago, IL	115	A
Chicago, IL	128	BBB
Washington, DC	82	DC
Washington, DC	87	AA

Want:

City	Building #	Specs
San Francisco, CA	121	A
San Francisco, CA	154	BB
San Francisco, CA	225	DC
Athens, GA	2568	AA
Chicago, IL	115	A
Chicago, IL	128	BBB
Washington, DC	82	DC
Washington, DC	87	AA

Re: Unmerging Excel cells in SAS

Reeza — Tue, 14 Jan 2014 21:59:09 GMT

How are you exporting the data, assuming ODS Tagsets, but proc report, proc print or proc tabulate?

Re: Unmerging Excel cells in SAS

sarahsasuser — Tue, 14 Jan 2014 22:18:30 GMT

I'm importing the excel file using proc import, manipulating the data so that it looks like the want dataset, then exporting to a csv file via proc export.

Re: Unmerging Excel cells in SAS

Reeza — Tue, 14 Jan 2014 22:31:08 GMT

So the data imported looks like the have dataset and you'd like to correct it to the want data set?

If so look at the retain statement in the processing.

proc sort data=sashelp.class out=class;

by sex;

run;

data have;

set class;

by sex;

if not first.sex then sex='';

run;

data want;

set have;

retain sex2;

if sex ne '' then sex2=sex;

if sex='' then sex=sex2;

run;

data want_wrong;

set have;

retain sex;

run;

Re: Unmerging Excel cells in SAS

data_null__ — Tue, 14 Jan 2014 22:31:22 GMT

data mergecell;
   input City &$30. Building Specs $;
   cards; 
San Francisco, CA  121  A
.                  154  BB
.                  225  DC
Athens, GA         2568 AA
Chicago, IL        115  A
.                  128  BBB
Washington, DC     82   DC
.                  87   AA
;;;;
   run; 
data necessaryevil / view=necessaryevil;
   retain _one_ 1; 
   set mergecell;
   run; 

data unmergedcell;
   update necessaryevil(obs=0) necessaryevil;
   by _one_;
   output; 
   drop _one_;
   run; 
proc print; 
   run;

Re: Unmerging Excel cells in SAS

gupta_shubham — Thu, 16 Jan 2014 12:30:02 GMT

Hi,

I am facing the problem stated below. Please help me out

I am solving a optimization problem where I am using proc fcmp to define objective function. I need to call a macro from proc fcmp and I am using run_macro for that.

So overall inside proc optmodel, I am calling proc fcmp and proc fcmp in turn is calling a macro using "run_macro".

But I am getting an error show below after compiling the code:

ERROR: Function RUN_MACRO not found.

ERROR: Failed to compile functions

options mprint ;

options mlogic ;

options symbolgen;

libname ilib "C:\Users\idnshg\Desktop\temp\date\16.1\OR";

%macro abc();

data _null_;

z=&x+&y;

call symput('z',z);

run;

%mend abc;

proc fcmp outlib=work.functions.wrapper;

function test_sum(x,y);

rc=run_macro('abc',x,y,z);

return(z);

endsub;

run;

options cmplib=work.functions;

/*test prog for sas or*/

proc optmodel;

var x, y;

min f =x**2-x-2*y-x*y+y**2+(test_sum(x,y));

solve;

print x y;

quit;

Re: Unmerging Excel cells in SAS

sarahsasuser — Thu, 16 Jan 2014 15:44:03 GMT

Thanks data_null_, this worked pretty well. However, the output I got in the want dataset has the last row repeated >30 times. There are also duplicates within the file. This may be because the updated excel spreadsheet I received has more than one merged cell in it. How do I account for those?

Thanks!

Have

City	Other variable1	Building #	Specs	Other variable 2
San Francisco, CA	xxxx	121	A	No
		154	BB
		225	DC
Athens, GA	x	2568	AA	No
Chicago, IL	zzz	115	A	Yes
Chicago, IL	zzz	128	BBB	Yes
Washington, DC	yyy	82	DC	No
Washington, DC	yyy	87	AA	No

Re: Unmerging Excel cells in SAS

data_null__ — Thu, 16 Jan 2014 15:51:29 GMT

Does the input file have 30 rows that are all blank. Import from EXCEL often produces many rows of all missing as you describe. Process your input to remove those then apply the UPDATE. I don't understand what you are saying about duplicates.

Re: Unmerging Excel cells in SAS

sarahsasuser — Thu, 16 Jan 2014 16:27:09 GMT

Thanks! That worked. Once I specified the range I didn't have any issues.

Re: Unmerging Excel cells in SAS

New_Try — Thu, 16 Jan 2014 18:00:40 GMT

You can have the "Want" file in Excel by using function =IF(LEN(A3)=0,D2,A3) in column D. Assume City is in column A and first data row is on row 3.

Re: Unmerging Excel cells in SAS

gupta_shubham — Thu, 16 Jan 2014 19:58:28 GMT

HI Data_null_,

Please help me out with the problem mentioned in point number: 5.

Thanks

Shubham Gupta

Re: Unmerging Excel cells in SAS

Reeza — Thu, 16 Jan 2014 20:00:14 GMT

Please post your question as a new thread, not in someone else's, especially when the topic is not related at all.