The SAS Output Delivery System and reporting techniques

Unmerging Excel cells in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

Unmerging Excel cells in SAS

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:

CityBuilding #Specs
San Francisco, CA121A
154BB
225DC
Athens, GA2568AA
Chicago, IL115A
128BBB
Washington, DC82DC
87AA

Want:

CityBuilding #Specs
San Francisco, CA121A
San Francisco, CA154BB
San Francisco, CA225DC
Athens, GA2568AA
Chicago, IL115A
Chicago, IL128BBB
Washington, DC82DC
Washington, DC87AA

Accepted Solutions
Solution
‎01-16-2014 10:51 AM
Respected Advisor
Posts: 3,799

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser

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.

View solution in original post


All Replies
Super User
Posts: 19,832

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser

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

Frequent Contributor
Posts: 106

Re: Unmerging Excel cells in SAS

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.

Super User
Posts: 19,832

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser

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;

Respected Advisor
Posts: 3,799

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser
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;
Occasional Contributor
Posts: 19

Re: Unmerging Excel cells in SAS

Posted in reply to data_null__

Hi,

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

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;

Super User
Posts: 19,832

Re: Unmerging Excel cells in SAS

Posted in reply to gupta_shubham

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

Frequent Contributor
Posts: 106

Re: Unmerging Excel cells in SAS

Posted in reply to data_null__

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

CityOther variable1Building #SpecsOther variable 2
San Francisco, CAxxxx121ANo
154BB
225DC
Athens, GAx2568AANo
Chicago, ILzzz115AYes
128BBB
Washington, DCyyy82DCNo
87AA
Solution
‎01-16-2014 10:51 AM
Respected Advisor
Posts: 3,799

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser

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.

Frequent Contributor
Posts: 106

Re: Unmerging Excel cells in SAS

Posted in reply to data_null__

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

Occasional Contributor
Posts: 19

Re: Unmerging Excel cells in SAS

Posted in reply to data_null__

HI Data_null_,

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

Thanks

Shubham Gupta

New User
Posts: 1

Re: Unmerging Excel cells in SAS

Posted in reply to sarahsasuser

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 1891 views
  • 5 likes
  • 5 in conversation