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 a dataset from my client that I need to reformat so that I can do some analysis and create some reports with it. It is in Excel in the following structure:

ChicagoDC
Building25156548852665
Phase 15/27/20117/5/20123/30/2012
Phase 26/30/20118/10/20124/4/2012
Phase 37/15/20118/1/20124/11/2012
Time from Phase 1 to Phase 3182512

I want it in this format (I will calculate the time in days myself)

BuildingPhase 1Phase 2Phase 3City
251565/27/20116/30/20117/15/2011Chicago
548857/5/20128/10/20128/1/2012Chicago
26653/30/20124/4/20124/11/2012DC

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
AncaTilea
Pyrite | Level 9

Hi.

Assuming that you can 'unmerge' the cell named 'Chicago' to 'Chicago' 'Chicago', or 'Chicago' 'Chicago1' then this may do what you need:

proc import out = your_data

            datafile= "path\data.xls"

            dbms = excel replace;

     MIXED=YES; *this is important because in the city variable(s) you have dates and building codes;

run;

*now we transpose and use the ID to label the columns;

proc transpose data = your_data out = my_data;id var1;var _all_ ;run;

*clean up the data;

data want(rename = (_name_ = city));

    set my_data(where = (_name_ ne "var1") drop = _label_ );

label _name_ = "City";

run;

Let me know what you think.

Anca.

View solution in original post

4 REPLIES 4
AncaTilea
Pyrite | Level 9

Hi.

Assuming that you can 'unmerge' the cell named 'Chicago' to 'Chicago' 'Chicago', or 'Chicago' 'Chicago1' then this may do what you need:

proc import out = your_data

            datafile= "path\data.xls"

            dbms = excel replace;

     MIXED=YES; *this is important because in the city variable(s) you have dates and building codes;

run;

*now we transpose and use the ID to label the columns;

proc transpose data = your_data out = my_data;id var1;var _all_ ;run;

*clean up the data;

data want(rename = (_name_ = city));

    set my_data(where = (_name_ ne "var1") drop = _label_ );

label _name_ = "City";

run;

Let me know what you think.

Anca.

sarahsasuser
Quartz | Level 8

Thanks Anca, this worked well. However, the City column shows the variables as Chicago, Chicago1 Chicago2, DC, DC1, DC2, etc. Is there a way to get rid of the _# after the city?

AncaTilea
Pyrite | Level 9

Hi.

One way to replace the digits is to use regular expressions:

data want;

     set have;

new_city = put(prxchange('s/[0-9]//', -1, city), $10.);

run;

Good luck!

Anca.

sarahsasuser
Quartz | Level 8

Thanks Anca.

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
  • 4 replies
  • 948 views
  • 1 like
  • 2 in conversation