topic Adding States to Dataset in SAS Programming

Adding States to Dataset

dpachorek — Wed, 30 Sep 2020 20:48:50 GMT

Hi!

I have a dataset of degree programs and am trying to setup a dataset to manipulate into visualizations on Tableau. In order to get the data where I want it, I need to add a state variable and each of the 47 programs need to have all 50 US states listed for it (2,350 rows).

Is there a way to add these in quickly with a SAS program so that I don't have to do this manually in Excel?

Thanks!

Re: Adding States to Dataset

Reeza — Wed, 30 Sep 2020 20:56:22 GMT

Check the SASHELP or SASMAPS library for the State level datasets you can merge (left join) your results with to get the data you need.

Depending on exactly what you're doing you may also want to look into CLASSDATA or PRELOADFMT.

Re: Adding States to Dataset

jimbarbour — Wed, 30 Sep 2020 21:08:49 GMT

So, you have a dataset containing information on 47 degree programs. You need to add all 50 states to each degree.

I think just taking the Cartesian product via SQL should do it pretty simply and easily.

Something like this, although you'd have to use real state codes or state names and real degree names. I just used dummy names, but the SQL should be essentially the same.

Jim

data	Degrees;
	drop	_:;
	DO	_I	=	1	TO	47;
		Degree_name	= CATS('Degree', put(_i, 2.));
		OUTPUT;
	END;
run;

DATA	States;
	drop	_:;
	DO	_I	=	1	TO	50;
		State_Code	= CATS('State', put(_i, 2.));
		OUTPUT;
	END;
run;

PROC	SQL;
	CREATE TABLE	Degrees_With_States	AS
		SELECT	Degree_Name
				,State_Code
			from	degrees, states;	
QUIT;

Re: Adding States to Dataset

jimbarbour — Wed, 30 Sep 2020 21:21:01 GMT

Excellent Idea from @Reeza. My solution is the same: Use SQL to create the Cartesian product, but the State table can be fairly easily created from SAShelp.GCstate. GCstate includes territories like GU, PR, VI, etc. and Washington DC ("DC"), but you can screen out the ones you don't want fairly easily.

Code changed as follows:

data	Degrees;
	drop	_:;
	DO	_I	=	1	TO	47;
		Degree_name	= CATS('Degree', put(_i, 2.));
		OUTPUT;
	END;
run;

/*DATA	States;*/
/*	drop	_:;*/
/*	DO	_I	=	1	TO	50;*/
/*		State_Code	= CATS('State', put(_i, 2.));*/
/*		OUTPUT;*/
/*	END;*/
/*run;*/

PROC	SQL;
	CREATE	TABLE	States	as
		SELECT	DISTINCT	MapIDNameAbrv	as	State_Code
			FROM	SAShelp.GCSTATE
				WHERE	ISOalpha3	=	'USA';
QUIT;

PROC	SQL;
	CREATE TABLE	Degrees_With_States	AS
		SELECT	Degree_Name
				,State_Code
			from	degrees, states;	
QUIT;

Jim