BookmarkSubscribeRSS Feed
forumsguy
Fluorite | Level 6

Hi there,

My task is to create individual datasets from the variable of my dimension. However I am facing issue with values which has single quotes... Please find the code below

Data Dimension;

input Name $40.;

datalines;

property

worker's compensation

calamity

Life's data

;

run;

Proc sql;

select distinct Name into : nm separated by '*' from dimension;

select count distinct(Name) into :count from dimension;

QUIT;

%Macro m1;

%do i=1 %to count1;

%let nm1=%scan(%superquote(nm),&i,'*');

data new&i;

set Fact;

where Fact_name="&nm1";

run;

%end;

%mend;

%m1;

Here my nm1 resolves for property but not for worker's compensation... has anybody encountered any problem like this before... any help is really appreciated...

7 REPLIES 7
Ksharp
Super User

I found several problems in your code.

data fact;
input fact_Name $40.;
datalines;
property
worker's compensation
calamity
Life's data
;
run;



Data Dimension;
input Name $40.;
datalines;
property
worker's compensation
calamity
Life's data
;
run;

 

Proc sql;
select distinct Name into : nm separated by '*' from dimension;
select count (distinct Name) into :count from dimension;
QUIT;


%Macro m1;
%do i=1 %to &count;
%let nm1=%qscan(%bquote(&nm),&i,*); 
data new&i;
set Fact;
where Fact_name="&nm1";
run;
%end;
%mend;

 

%m1

Ksharp

Ksharp
Super User

It is a simpler solution.

data fact;
input fact_Name $40.;
datalines;
property
worker's compensation
calamity
Life's data
;
run;



Data Dimension;
input Name $40.;
datalines;
property
worker's compensation
calamity
Life's data
;
run;

data _null_;
 set dimension;
 call execute(cats('data new',_n_,'; set fact; where fact_name="',name,'";run;'));
run;


Ksharp

Tom
Super User Tom
Super User

The QUOTE() function helps eliminate the need for macro quoting in situations like this.

proc sql noprint ;

  select distinct quote(trim(name)) into : nm separated by '*' from dimension;

%let count=&sqlobs;

quit;

%do i=1 %to &count;

data new&i;

  set Fact;

  where Fact_name=%scan(&nm,&i,*);

run;

%end;

Tom
Super User Tom
Super User

If you need to eliminate some I/O then you can actually create all of the datasets in a single pass.

data %do i=1 %to &count; new&i %end; ;

  set Fact;

%do i=1 %to &count;

  if Fact_name=%scan(&nm,&i,*) then output new&i;

%end;

run;

Linlin
Lapis Lazuli | Level 10

Hi Tom,

why "if Fact_name=%scan(&nm,&i,*) then output new&i;"

not

"if Fact_name=%scan(&nm,&i,"*") then output new&i;" (it doesn't work) ?

Thank you!  - Linlin

Tom
Super User Tom
Super User

In macro code everything is a string. So you do not need to quotes things. And if you do the quotes become part of the string.

In this case the it will break if you include the quotes in the delimiter list for the %SCAN() function. Instead of generating

  fact_name="Life's data"

it would generate

   fact_name=Life's data

Which would both have unbalanced single quotes and be invalid SAS syntax.

Linlin
Lapis Lazuli | Level 10

Thank you Tom!  I will try to remember that.   - Linlin

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
  • 7 replies
  • 1402 views
  • 0 likes
  • 4 in conversation