how to remove comma

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

how to remove comma

Hi

My code is below :

 

data new;
infile "F:\Raw.txt";
input Id Name $ +7 Incentive : percent. Salary $ 6-12;
run;

 

how should i remove comma in salary. Can we use commaW. in the above code if yes then how ?. Kindly help. I am attaching the raw data file. 

 


Accepted Solutions
Solution
‎04-23-2017 05:26 AM
Trusted Advisor
Posts: 1,137

Re: how to remove comma

[ Edited ]
data new;
infile cards missover;
input Id @3 Name $3.  @14 Incentive  :percent3.   @7 salary   :comma6. ;
cards;
1 Ram 25,000 40%
2 XYZ 31,000 25%
3 ABC 42,000 46%
4 MMN 52,000 20%

; run;
Thanks,
Jag

View solution in original post


All Replies
Occasional Contributor
Posts: 11

how to remove comma

[ Edited ]

My code is below

 

data new;
infile "F:\Raw.txt";
input Id Name $ +7 Incentive : percent. Salary $ 6-12;
run;

 

i have attached my raw file. I want to know how should i remove comma from salary. I have considered incentive after name and want to mention salary after incentive. Although in the raw data the sequence is different. 

PROC Star
Posts: 276

Re: how to remove comma

You cannot use column input to read nonstandard data such numbers that has comma. You would need a formatted input like:

input @(column position) Salary comma6.; /*Column position is the column pointer to which the input pointer should be instructed to move in the input buffer and start reading values from that point.*/

 

HTH,

Naveen Srinivasan

Occasional Contributor
Posts: 11

Re: how to remove comma

Hi Naveen,

 

In the raw data below

 

1 Ram 25,000 40%  
2 XYZ 31,000 25% 
3 ABC 42,000 46% 
4 MMN 52,000 20% 

 

I want to get table like below

 

First column: ID

Second Column: Name

Third Column: Incentive although it is the fourth column in Raw data

Fourth Column: Salary, although it is the third column in Raw data that also without comma.

 

How to do it ?

Super User
Posts: 7,431

Re: how to remove comma


manish_1 wrote:

Hi Naveen,

 

In the raw data below

 

1 Ram 25,000 40%  
2 XYZ 31,000 25% 
3 ABC 42,000 46% 
4 MMN 52,000 20% 

 

I want to get table like below

 

First column: ID

Second Column: Name

Third Column: Incentive although it is the fourth column in Raw data

Fourth Column: Salary, although it is the third column in Raw data that also without comma.

 

How to do it ?


Before the input statement, add a format or length statement with the variables in the desired order (the format statement does not need to contain formats).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,845

Re: how to remove comma

This is good example of why you should always define your table structure and variables instead of letting SAS guess at what definition you intended based when you first reference the variable. Use the LENGTH or ATTRIB statement to define the variables. You can also assign INFORMAT to make the INPUT statement easier.

So in this case define the variables in the order you want and then on the INPUT statement read them in the order they appear in the raw data.

data want;
  length id 8 name $20 incentive salary 8 ;
  informat incentive percent. salary comma. ;
  input id name salary incentive;
cards;
1 Ram 25,000 40%  
2 XYZ 31,000 25% 
3 ABC 42,000 46% 
4 MMN 52,000 20% 
;

Capture.PNG

Trusted Advisor
Posts: 1,137

Re: how to remove comma

Please try the comma.

 

data new;
infile cards missover;
input Id Name $3. Incentive :comma6. ;
cards;
1 Ram 25,000 
2 xyz 31,000 
3 Abc 42,000 
4 MMN 52,000 
;
run;
Thanks,
Jag
Solution
‎04-23-2017 05:26 AM
Trusted Advisor
Posts: 1,137

Re: how to remove comma

[ Edited ]
data new;
infile cards missover;
input Id @3 Name $3.  @14 Incentive  :percent3.   @7 salary   :comma6. ;
cards;
1 Ram 25,000 40%
2 XYZ 31,000 25%
3 ABC 42,000 46%
4 MMN 52,000 20%

; run;
Thanks,
Jag
Occasional Contributor
Posts: 11

Re: how to remove comma

ThanksSmiley Happy

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 708 views
  • 1 like
  • 5 in conversation