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

data test1:
ID="0000102+";
SEQ_NO="000932+";
BALANCE="003234.32+";
DTM='09mAY2017:02:00:00'DT;
FORMAT DTM DATETIME20.;
RUN;

I have to modify above TEST1 dataset in two scenarios.
1. I need to remove end + signs in Character variables;
2. I need to modify those character variables into numeric variables.

 

I have used below program to accomplish.

It's not working. Please let me know the how to correct the code.

 

data test;

attrib

id     length=8 format=19. informat=19.

seq_no length=8 format=19. informat=19.

balance length=8 format=22.2 informat=22.2 ;

set test1 (rename=(id=id_ seq_no=seq_no_ balance=balance_));

array col_name_old {3} id_ seq_no_ balance_;

array col_name_new {3} id seq_no  balance;

do i=1 to 3;

   if find(col_name_old{i}, "+") = length(col_name_old{i}) then do;

      col_name_old{i} = compress(col_name_old{i},"+");

     col_names_new{{i} = col_name_old{i};

   end;

end;

run;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@Kurt_Bremser: My error. There was a typo in my code. I had used (as posted):

  balance=input(balance,trailsgn10.);

rather than:

  balance=input(_balance,trailsgn10.);

Interestingly, that doesn't produce an uninitialized note.

 

Art, CEO, AnalystFinder.com

View solution in original post

10 REPLIES 10
art297
Opal | Level 21

You can use arrays but, for only 3 variables, they aren't needed. e.g.:

 

data test1;
  ID="0000102+";
  SEQ_NO="000932+";
  BALANCE="003234.32+";
  DTM='09mAY2017:02:00:00'DT;
  FORMAT DTM DATETIME20.;
RUN;

data test (drop=_:);
  set test1 (rename=(id=_id seq_no=_seq_no balance=_balance));
  id=input(compress(_id,"+"),8.);
  balance=input(compress(_balance,"+"),8.);
  seq_no=input(compress(_seq_no),8.);
run;

Art, CEO, AnalystFinder.com

 

art297
Opal | Level 21

@Kurt_Bremser: Interestingly, for ID the trailsgn informat fails unless one specifies a width of 7 or more, and doesn't work at all if the field contains a decimal point (like, in this case, balance).

 

Art, CEO, AnalystFinder.com

 

 

Kurt_Bremser
Super User

@art297 wrote:

@Kurt_Bremser: Interestingly, for ID the trailsgn informat fails unless one specifies a width of 7 or more, and doesn't work at all if the field contains a decimal point (like, in this case, balance).

 

Art, CEO, AnalystFinder.com

 

 


I have to differ.

Run this:

data test1;
input id trailsgn10.;
cards;
0000102+
000932+
003234.32+
;
run;

data test2;
input _id $10.;
id = input(_id,trailsgn10.);
cards;
0000102+
000932+
003234.32+
;
run;

Both steps produce correct numerical values.

 

Edit: fixed a typo.

art297
Opal | Level 21

@Kurt_Bremser: The problem is with the Balance field. I ran the following on SAS UE:

data test2 (drop=_:);
  set test1 (rename=(id=_id seq_no=_seq_no balance=_balance));
  id=input(_id,trailsgn7.);
  balance=input(balance,trailsgn10.);
  seq_no=input(_seq_no,trailsgn8.);
run;

Of course, since the defalut is only 6, the width has to be specified for each variable. However, BALANCE results in a missing value.

 

Art, CEO, AnalystFinder.com

 

Kurt_Bremser
Super User

Not possible for the value of balance as specified in the OP and used in my last post.

Run this:

data test;
_balance = "003234.32+";
balance = input(_balance,trailsgn10.);
run;

proc print data=test noobs;
run;

Result:

 _balance     balance

003234.32+    3234.32

No missing value there.

art297
Opal | Level 21

@Kurt_Bremser: My error. There was a typo in my code. I had used (as posted):

  balance=input(balance,trailsgn10.);

rather than:

  balance=input(_balance,trailsgn10.);

Interestingly, that doesn't produce an uninitialized note.

 

Art, CEO, AnalystFinder.com

nbonda
Obsidian | Level 7

Thank you. It works. I never knew there is a informat like trailsgn.

Kurt_Bremser
Super User

The "unitialized" NOTE is not created dynamically, but syntactically while the step is compiled. Since the compiler finds an assignment to balance, it does not issue a NOTE; obviously it does not check if it's a recursive assignment.

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
  • 10 replies
  • 1682 views
  • 0 likes
  • 3 in conversation