Unmerging Excel cells in SAS

[sarahsasuser]

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
	128	BBB
Washington, DC	82	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

[Reeza]

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

[sarahsasuser]

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.

[Reeza]

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;

[data_null__]

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;

[gupta_shubham]

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;

[Reeza]

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

[sarahsasuser]

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
		128	BBB
Washington, DC	yyy	82	DC	No
		87	AA

[data_null__ · Accepted solution]

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.

[sarahsasuser]

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

[gupta_shubham]

HI Data_null_,

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

Thanks

Shubham Gupta

[New_Try]

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.