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

Cocat Numerics.png

 

Hi everyone who is reading this 🙂

 

I have 1 char colum (source) with values (D_RangeL and D_RangeH) and 1 numeric column with values (2,81 and 9,37 = european notation, no dot, but comma!). See the picture where it says Have.

 

Now what I want to do in Base SAS is: I want to concatenate the row values to 1 value. So it is like in the picture where it says WANT.

Is that possible in SAS? I did this example in Excel, as you might have noticed...because in SAS I can;t manage this.

I used

catx(' - ', RangeL, RangeH);

but because this becomes a char value and I want it in a Num column this is not possible in SAS? Or...

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You will need to create a CHARACTER variable to hold a string like that.

You can use the COMMAX format to generate numbers that use comma instead of period as the decimal point.  Although it might be based on your LOCALE setting that COMMA format will do that for you instead.

data have ;
  input name $ value ;
cards;
RangeL 2.81
RangeH 9.37
;
proc transpose data=have out=wide (drop=_name_) ;
  id name ;
  var value ;
run;

data want ;
  set wide ;
  length cvalue $30 ;
  cvalue = catx(' - ',put(rangel,commax10.2),put(rangeh,commax10.2));
  format range: commax10.2 ;
run;

proc print width=min;
run;
       Range    Range
Obs      L        H        cvalue

 1     2,81     9,37     2,81 - 9,37

View solution in original post

9 REPLIES 9
Reeza
Super User

It’s not possible to have characters in a numeric variable. 

 

Depending on what you’re ultimately trying to do there may be other workarounds, but CATX is the correct approach for a character variable. 

 

Excel doesn’t force types on columns, which is why it’s allowed there but in general most Datsbass systems don’t allow this type of thing. 


@SAS_Question wrote:

Cocat Numerics.png

 

Hi everyone who is reading this 🙂

 

I have 1 char colum (source) with values (D_RangeL and D_RangeH) and 1 numeric column with values (2,81 and 9,37 = european notation, no dot, but comma!). See the picture where it says Have.

 

Now what I want to do in Base SAS is: I want to concatenate the row values to 1 value. So it is like in the picture where it says WANT.

Is that possible in SAS? I did this example in Excel, as you might have noticed...because in SAS I can;t manage this.

I used

catx(' - ', RangeL, RangeH);

but because this becomes a char value and I want it in a Num column this is not possible in SAS? Or...

 

 

 


 

SAS_Question
Quartz | Level 8

Thank you for your confirmation. That's what I was afraid for...

Now, that we know that Excel doesn't matter what you put in the cell (char of num), is it possible to do this when I export the SAS table data to excel? 

 

Can I say to SAS, when you come across row (D_RangeL and D_RangeH) keep the value in mind, and when exporting export these values as one row and as a char: "D_RangeL - D_RangeH" (in values: 2,81 - 9,37)

 

I really don't know where to start here.. 🙂

Some help would be nice guys. Thanks again.

 

Reeza
Super User

Excel won’t consider it a numeric variable either, but it doesn’t force a column to adhere to rules. you can check that in Excel using ISNUMBER() function. 

 

You can export the values to Excel, is that not working?

 

If you need help you need to explain what you’re trying to do, what you have or want. So far all you’ve asked is if things are possible. 

Also, not a guy. 

 


@SAS_Question wrote:

Thank you for your confirmation. That's what I was afraid for...

Now, that we know that Excel doesn't matter what you put in the cell (char of num), is it possible to do this when I export the SAS table data to excel? 

 

Can I say to SAS, when you come across row (D_RangeL and D_RangeH) keep the value in mind, and when exporting export these values as one row and as a char: "D_RangeL - D_RangeH" (in values: 2,81 - 9,37)

 

I really don't know where to start here.. 🙂

Some help would be nice guys. Thanks again.

 


 

SAS_Question
Quartz | Level 8

Hi Reeza,

 

sorry about that 'guys' statement.. it is a thing i always use.

Consider it a statement of concatenation of boys and girls, though this is also not possible! 😉

 

As for the export:

Just consider the 2 given columns in the picture and the 2 rows, which need to be exported like on the picture where it says WANT. 

That's what I want to do.

 

What I do to get these kind of table is (very simplified now):

I import 2 csv files. Do some sorting and selection on them by proc sort and proc sql.
After that I get a table which I have to transpose (thus getting automatically num and char columns).

Till then everything is going okay.

So supose then this is a very simplified transposed table which I get, and which I can succesfully export to my excel sheet:

 

data x;
infile datalines delimiter=','; 
   input source $10. value;
   datalines;                      
		D_RangeH, 10
		D_RangeL, 9
;
run;

proc sort data=x;
  by source descending value;
run;

proc export data=x outfile="p:\temp\test.xlsx" dbms=xlsx replace;
run;

 

No problems so far.

But now, dear friends, what I want to do is concat the 2 rows

        D_RangeH   10
        D_RangeL    9

To 1 Row, like this:

        D_RangeLH    9 - 10

 

But I really dont have a clue where to start.. 

So any help with this step would be nice. Thank you in advance.

I hope you understand what I mean now. If not, let me know again.

 

Reeza
Super User

After that I get a table which I have to transpose (thus getting automatically num and char columns).

Till then everything is going okay.

 

I don't see any transpose in your code, but you say that's going okay? Did you miss including the PROC TRANSPOSE?

SAS_Question
Quartz | Level 8

Consider this simplified step:

 

data x;
infile datalines delimiter=','; 
   input source $10. value;
   datalines;                      
		D_RangeH, 10
		D_RangeL, 9
;
run;

 

as the result for all my steps (incl. the transpose step). So that's all okay.

No problems there.

 

The question is:

How do I concatenate the 2 row values to 1 row with concatenated value of 9 - 10.

That's all I want to know, in fact. Excuse all the other context! 🙂

 

Reeza
Super User

Your code doesn't work. Please test it in the future. 

 

Here's one way. 

 

data want;
merge x (obs=1)
    x (firstobs=2 rename= (value=prev_value source=prev_source));
    
    want = catx('-', prev_Value, value);
run;
Tom
Super User Tom
Super User

You will need to create a CHARACTER variable to hold a string like that.

You can use the COMMAX format to generate numbers that use comma instead of period as the decimal point.  Although it might be based on your LOCALE setting that COMMA format will do that for you instead.

data have ;
  input name $ value ;
cards;
RangeL 2.81
RangeH 9.37
;
proc transpose data=have out=wide (drop=_name_) ;
  id name ;
  var value ;
run;

data want ;
  set wide ;
  length cvalue $30 ;
  cvalue = catx(' - ',put(rangel,commax10.2),put(rangeh,commax10.2));
  format range: commax10.2 ;
run;

proc print width=min;
run;
       Range    Range
Obs      L        H        cvalue

 1     2,81     9,37     2,81 - 9,37
SAS_Question
Quartz | Level 8

Again sorry @Reeza: the code worked in Enterprise Guide here... Maybe not in Base SAS only?

 

@Tomand @Reeza both very beautiful solutions provided.

Now how do I select both as solution for my question?

Help! 🙂

You both deserve it!

 

 

 

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
  • 9 replies
  • 9692 views
  • 3 likes
  • 3 in conversation