topic how to remove comma in SAS Programming

how to remove comma

manish_1 — Sun, 23 Apr 2017 07:14:08 GMT

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.

how to remove comma

manish_1 — Sun, 23 Apr 2017 07:09:02 GMT

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.

Re: how to remove comma

novinosrin — Sun, 23 Apr 2017 07:17:17 GMT

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

Re: how to remove comma

manish_1 — Sun, 23 Apr 2017 07:30:57 GMT

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 ?

Re: how to remove comma

Jagadishkatam — Sun, 23 Apr 2017 07:41:49 GMT

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;

Re: how to remove comma

Jagadishkatam — Sun, 23 Apr 2017 08:00:18 GMT

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;

Re: how to remove comma

Kurt_Bremser — Sun, 23 Apr 2017 08:08:27 GMT

@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).

Re: how to remove comma

manish_1 — Sun, 23 Apr 2017 09:53:38 GMT

Thanks

Re: how to remove comma

Tom — Sun, 23 Apr 2017 12:50:04 GMT

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% 
;