Desktop productivity for business analysts and programmers

creating datasets based on the varible values

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

creating datasets based on the varible values

if anyone having a program for creating a datsets based on the varible values,pls share me  or   modify my below code.

interested to know modifcation of below code,  so that  i can understand where I done mistake.

1.   to get a list of varible values into a macro varible

proc sql;

select distinct make into : makes separated by ' ' from sashelp.cars where make not like '%-%'order by make;

quit;

2. creating different datasets based on the varible values;

proc sort data=sashelp.cars out=cars;

by make;

run;

data &makes;

set cars;

by make;

retain i 1;

localmakes=&makes;

put localmakes;

if first.make  then

  do;       

  output scan(localmakes,i,' ');

  i+1;

  end;

run;

how to print  macro varible values line by line( macrovarible =1 2 3) want output like

1

2

3


Accepted Solutions
Solution
‎10-06-2014 05:41 AM
Super User
Posts: 1,116

Re: creating datasets based on the varible values

Please try the below code. hope this is what you are trying to achieve.

data class;

set sashelp.class;

run;

proc sql;

select distinct name into:name1-:name&sysmaxlong from class;

quit;

%macro test;

%do i = 1 %to &sqlobs;

data &&name&i;

set class;

where name = "&&name&i";

run;

%end;

%mend;

%test;

The above code will generate a separate dataset for every value.

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Solution
‎10-06-2014 05:41 AM
Super User
Posts: 1,116

Re: creating datasets based on the varible values

Please try the below code. hope this is what you are trying to achieve.

data class;

set sashelp.class;

run;

proc sql;

select distinct name into:name1-:name&sysmaxlong from class;

quit;

%macro test;

%do i = 1 %to &sqlobs;

data &&name&i;

set class;

where name = "&&name&i";

run;

%end;

%mend;

%test;

The above code will generate a separate dataset for every value.

Thanks,

Jag

Thanks,
Jag
Regular Contributor
Posts: 165

Re: creating datasets based on the varible values

Great, it's working!

Could you please tell me why we've used double ampersand in data step below?

data &&name&i;

set class;

where name = "&&name&i";

Super User
Posts: 1,116

Re: creating datasets based on the varible values

The reason for using it is , if i use &name&i it will try to resolve &name first and then &i, however since we do not have any values for &name, we will get a warning. So if we place &&name&i, this will resolve to &name1 first and then &name1 will resolve to the name.

Hope this was helpful.

Thanks,

Jag

Thanks,
Jag
Regular Contributor
Posts: 165

Re: creating datasets based on the varible values

Thanks! It was helpful.

Contributor
Posts: 44

Re: creating datasets based on the varible values

thanks.

sorry to ask in this way.

1. if you can could you help me in changing my code to do the same task(without macro) with a datastep.

2. how to print macro varible vales one by one.

  • ex:
  • &let numbers =1 2 3;
  • %put numbers;   --> need output like this.how to print this way/
  • 1
  • 2
  • 3
Grand Advisor
Posts: 9,584

Re: creating datasets based on the varible values

If data was not very very big .

data class;
 set sashelp.class;
run;

proc sort data=class;by sex;run;
data _null_;
 if _n_ eq 1 then do;
  if 0 then set class;
  declare hash ha(multidata:'y');
   ha.definekey('sex');
   ha.definedata('name','age','sex','weight','height'); 
   ha.definedone();
end;
set class;
by sex;
ha.add();
if last.sex then do; ha.output(dataset: sex);ha.clear(); end;
run;

Xia Keshan

Contributor
Posts: 44

Re: creating datasets based on the varible values

hi ksharp,

please help me in this.(you can copy and paster this code as it is in your loca). got a macro for the same task which is working fine. but I want to understand where I am doing mistake in the following approach.

* selecting all varible values from a varible in a table;

proc sql;

select distinct make into : makes separated by ' ' from sashelp.cars order by make;

quit;

* due to variable value  mercedes-benz compressing and printing the values;

%let makes=%sysfunc(compress(&makes,'-')); 

%put &makes; 

*  sorting the dataset to get the different values of varibles one by one(group processing);

proc sort data=sashelp.cars out=cars;

by make;

run;


* want to get each distinct value from varible and making  output to corresponding dataset.

data &makes;

set cars;

by make;

retain i 1;

if first.make  then

  do;

                output scan("&makes",i,' ');

  i+1;

  end;

run;

Grand Advisor
Posts: 9,584

Re: creating datasets based on the varible values

proc sql ;
select distinct compress(make,,'ka') into : makes separated by ' ' from sashelp.cars ;
select distinct 'when("'||strip(make)||'") output '||compress(make,,'ka') into : selects separated by ';' from sashelp.cars ;
quit;


data &makes;
set sashelp.cars;
select(make);
&selects ;
otherwise;
end;
run;

Xia Keshan

Contributor
Posts: 44

Re: creating datasets based on the varible values

thanks a lot.

A problem can be solved in many ways. generally your approach is simple and easy to understand.

Grand Advisor
Posts: 17,383

Re: creating datasets based on the varible values

Call Execute is also a power method:


proc sort data=sashelp.cars; by make; run;

data _null_;

set sashelp.cars;

by make;

if first.make then do;

     call execute (" data "|| compress(make, '-') || ";

  set sashelp.cars;

  where make=" || quote(make) ||";

  run;");

end;

run;

Contributor
Posts: 44

Re: creating datasets based on the varible values

hi,

thanks  a lot.

I thought some what wrong with the code due to color in editor. but executed fine and got the results.

I don't know whether you observed my code or not. could you pls help me , bcaz I tried to do with first.make but unable to do.

please observe my code and if you can please change my code

Attachment
Grand Advisor
Posts: 17,383

Re: creating datasets based on the varible values

The code is correct, the colour is due to text strings which the editor colours. If you don't want the colours don't use the enhanced editor.

Grand Advisor
Posts: 17,383

Re: creating datasets based on the varible values

Note the first suggestion of to NOT DO this in general:

Here's a variety of different ways if you choose to regardless.

Split Data into Subsets - sasCommunity

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 556 views
  • 7 likes
  • 5 in conversation