polingjw Tracker

Re: Automatic Directory Creation - Windows

polingjw — Tue, 31 Jan 2012 18:41:33 GMT

There is no need to use macro variables at all.

data _null_;

newdir=dcreate(catx('-', month(today()), put(today(), monyy7.)), 'C:\AB&C\Reports & Tests\');

run;

Re: Have PROC SQL within a Data Step??

polingjw — Mon, 30 Jan 2012 23:47:32 GMT

It is possible to use PROC SQL within a DATA step. Take a look at my South Central SAS User's Group paper from last year: http://www.scsug.org/SCSUGProceedings/2011/poling1/SQL%20Function.pdf. While the code presented in the paper only executes a SELECT statement, I think that you could modify the code to execute a CREATE TABLE statement.

Re: Lookup in SAS - Picking only first matched value

polingjw — Mon, 30 Jan 2012 13:30:01 GMT

If you really like using the Excel vlookup function, you could even create your own user-defined vlookup function with PROC FCMP. However, you should note that of all the solutions posed, my guess is that this one will be the least efficient by far.

%macro vlookup;

%let lookup_value = %sysfunc(dequote(&lookup_value));

%let lookup_table = %sysfunc(dequote(&lookup_table));

%let lookup_column = %sysfunc(dequote(&lookup_column));

%let return_column = %sysfunc(dequote(&return_column));

%let results = ;

data _null_;

if _n_=1 then set &lookup_table(keep=&lookup_column);

type = vtype(&lookup_column);

call symputx("type", type);

stop;

run;

data _null_;

set &lookup_table(obs = 1 keep=&lookup_column &return_column);

%if &type = N %then where &lookup_column = &lookup_value;;

%if &type = C %then where &lookup_column = "&lookup_value";;

call symputx("results", &return_column);

run;

%mend;

proc fcmp outlib=sasuser.funcs.general;

function vlookup(lookup_value $, lookup_table $, lookup_column $, return_column $) $;

length results $ 200;

rc=run_macro('vlookup', lookup_value, lookup_table, lookup_column, return_column, results);

return(results);

endsub;

quit;

data a;

input id visits : $5. @@;

datalines;

1 Two 2 Three 1 One 3 Two

;

data b;

input id location : $1. @@;

datalines;

1 A 2 B 3 A 1 C 2 B

;

run;

options cmplib=sasuser.funcs;

data ab;

set a;

location=vlookup(id, 'b', 'id', 'location');

run;

Re: Lookup in SAS - Picking only first matched value

polingjw — Thu, 26 Jan 2012 20:11:26 GMT

I believe that the answer is no, there is not a single procedure that does exactly what vlookup does. However, as demonstrated, there are many ways to get the desired results. Here is yet another way that does not require sorting:

data ab;

if _n_ = 1 then do;

if 0 then set b;

declare hash h(dataset:'b', multidata:'y');

h.definekey('id');

h.definedata('location');

h.definedone();

end;

set a;

if h.find() then call missing(location);

run;

Re: is there a way to calculate number of days in a month

polingjw — Wed, 25 Jan 2012 19:50:32 GMT

data dates;

input month $;

start = input(month, anydtdte.);

num_days = day(intnx('MONTH', start, 0, 'END'));

put (month num_days) (=);

datalines;

Jan11

Apr11

Feb11

Feb12

;

run;

PROC SQL & DELETE

polingjw — Mon, 23 Jan 2012 17:51:03 GMT

I don't have any Access 2010 applications to test on. Problem Note 9731 on the SAS support site might be of some help to you.

Re: List out all files in all sub directories

polingjw — Wed, 18 Jan 2012 17:05:13 GMT

Take a look at Jason Secosky's excellent paper "User-Written DATA Step Functions." Specifically, look at the "directory traversal example." I would include a link but copy/paste doesn't work for me in this forum.

Scatter Plot with 2 Categorical Variables

polingjw — Tue, 10 Jan 2012 17:20:15 GMT

That's a really creative solution. Thanks again!

Scatter Plot with 2 Categorical Variables

polingjw — Tue, 10 Jan 2012 17:18:10 GMT

I do have SAS 9.3 and your code is working. For some reason the plot symbols are are not displayed correctly in the HTML SAS result viewer. When I use a different ODS destination such as PDF, it works perfectly. Thanks!

Scatter Plot with 2 Categorical Variables

polingjw — Tue, 10 Jan 2012 16:42:22 GMT

Thank you. When I run the program, every age has a plot symbol of circle, so there is no way to distinguish between the ages. The documentation on the MARKERCOLORGROUP= option says that the option is ignored unless MARKERSYMBOL=CHARACTER.

Scatter Plot with 2 Categorical Variables

polingjw — Tue, 10 Jan 2012 16:36:57 GMT

I'll look into your suggestion about annotated markers. I've used the annotate facility to draw lines and insert text boxes, but not to change the symbols themselves. Yes, if you have some sample code that is relevant to this problem, I would like to see it. Thanks.

Scatter Plot with 2 Categorical Variables

polingjw — Tue, 10 Jan 2012 15:54:21 GMT

I want to create a scatter plot where the plot symbol values are determined according to the values of one categorical variable and the plot symbol colors are determined by another dichotomous categorical variable. For example, using the sashelp.class data set, suppose I want to make a simple scatter plot of height*weight. I want each age to be denoted with a different plot symbol, summarized in a legend. For example a dot for age 11, a square for age 12, etc. Furthermore, I want the color of the plot symbol to be blue for all males and red for all females. Therefore, a red square would indicate a 12 year old female and a blue square would indicate a 12 year old male. Is this possible? Any feasible solution would need to be generalized so that it could work with BY group processing. For example, if I had a data set with 20 classes of students with different ages and I wanted to create a separate graph for each class.

Thanks in advance.

Re: proc sql to select records from second row on

polingjw — Mon, 09 Jan 2012 11:23:42 GMT

Can't you just use the FIRSTOBS= data set option?

proc sql;

select id, dol

from example_data(firstobs=2)

where id=1;

quit;

Just note that the where clause is used to subset the data set before the FIRSTOBS= data set option is applied.

SAS7BDAT to MDB-table -- Speeding up?

polingjw — Wed, 04 Jan 2012 15:13:11 GMT

I don't think that the problem you are reporting is typical. I sometimes export data to Access 2003 from SAS and tables that are about 150-200MB take only a couple of minutes on my very modest PC using the default options. I typically create the table structure using Access and then use the ACCESS LIBNAME engine with PROC SQL INSERT statements to load the data into the tables.

problems of define a macro variable

polingjw — Thu, 29 Dec 2011 17:30:04 GMT

There were a couple of other errors in the original post (DO without END and ARRAY misspelled "arrat"). I suspect that rpg163 might have the same problem that I do with these forums - not being able to paste code into the editor.

problems of define a macro variable

polingjw — Thu, 29 Dec 2011 14:04:12 GMT

Try this and you will see:

%put col&num;

%let num=&num;

%put col&num;

Re: problems of define a macro variable

polingjw — Thu, 29 Dec 2011 13:57:30 GMT

%LET eliminates any leading and trailing blanks.

Facing problem while cut pasting files using X command

polingjw — Thu, 29 Dec 2011 13:18:42 GMT

I just thought of an easier solution. Just remove the single quotes from your original X statement, as this syntax is not required.

Re: Facing problem while cut pasting files using X command

polingjw — Thu, 29 Dec 2011 11:31:20 GMT

The problem is that the macro variables are not resolved inside the single quote marks. Try using this:

x %unquote(%str(%')move "C:\Data\Learning Products\&abc\&xyz" "C:\Data\Learning Products"%str(%'));

Re: split a sinlgle data set into 3 dataset at a time

polingjw — Thu, 22 Dec 2011 20:44:34 GMT

The solution I'm about to propose is not as efficient as the hash solution by Ksharp, I just think that it's a fun way to approach the problem. If the data set is large and efficiency is a concern, then I would have also used a hash solution. Before executing my solution, you have to first create a user-defined function with PROC FCMP, which is a one time endeavor. I call this the SQL function. After it is created, you could use this function in any program.

To create the function, you need to first save the following macro in your autocall library:

%macro sqlfunc;

%let sqlselect = %sysfunc(dequote(&sqlselect));

%let sqlresults = ;

proc sql noprint;

create view _TempView_ as &sqlselect;

select * into: sqlresults separated by " " from _TempView_;

drop view _TempView_;

quit;

%let sqlresults = &sqlresults;

%mend;

Next run the following FCPM procedure:

proc fcmp outlib=sasuser.funcs.general;

function sql(sqlselect $) $;

length sqlresults $ 32767; /* NOTE THAT IF YOU ARE USING SAS 9.2, THE LARGEST LENGTH YOU CAN USE HERE IS 260 */

rc=run_macro('sqlfunc', sqlselect, sqlresults);

return(sqlresults);

endsub;

quit;

Now you need to set the CMPLIB= option to tell sas where to look for previously compiled functions. I recommend doing this in your config file.

options cmplib=sasuser.funcs;

Now that the function has been defined, here is my solution to the problem:

data %sysfunc(sql(select distinct name from have));

set have;

array subjects{*} _numeric_;

do i=1 to dim(subjects);

subject=vname(subjects{i});

mark=subjects{i};

%sysfunc(sql(select distinct catx(' ', 'if name =', quote(strip(name)), 'then output', name, ';') from have));

end;

keep subject mark divison;

run;

Note that you could also use the SQL function in an expression or an assignment statement within the DATA step, without using %SYSFUNC.