BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sarahsasuser
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

11 REPLIES 11
Reeza
Super User

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

sarahsasuser
Quartz | Level 8

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
Super User

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__
Jade | Level 19
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
Obsidian | Level 7

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;

Reeza
Super User

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

sarahsasuser
Quartz | Level 8

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
data_null__
Jade | Level 19

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
Quartz | Level 8

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

gupta_shubham
Obsidian | Level 7

HI Data_null_,

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

Thanks

Shubham Gupta

New_Try
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 5565 views
  • 6 likes
  • 5 in conversation