options compress=yes;

/* Wyciagniecie wszystkich danych z pliku linia po linii*/
%macro getdata();
	data dict.full_data(keep=col1 prefix compress=yes);
		infile "C:\Users\splszw\Documents\EWID\plik swd ktory mamy docelowo zaimportowac\2261kopia.swd"
	    	firstobs = 2
			missover
			flowover
	    	DSD
	    	lrecl = 32767;
		format col1 $500.;
		format prefix $5.;

		input ;

		col1 = _infile_;
		temp = scan(col1,1,',');
		temp2 = scan(col1,1,';');

/*		if temp = "TD"*/
/*			then flag = 1;*/

		if temp in ("B","TD","TP","RO","RD","RP","RC","D","P","WG","W")
		then do;
			prefix = temp;
			output;
		end;

		else if temp2 in("X","GL","GX")
		then do;
			prefix = temp2;
			output;
		end;


	run;

%mend getdata;


/* Pobranie listy kolumn z prefiksem "B" i listy slownikow z prefiksem "TD" */
%macro get_col_list();

	data
		&dict_list(keep=dict_id dict_name)
		&col_list(keep=col_id col_name)
	;
		set dict.full_data(where=(prefix in ("TD","B","WG")));


		length dict_id $10;
		length dict_name $100;
		length col_id $10;
		length col_name $100;

			if scan(col1,1,',')="TD" then do;
				dict_id = scan(col1,2,',');
				dict_name = compress(tranwrd(trim(upcase(strip(scan(col1,2,';')))),' ','_'),"ABCDEFGHIJKLMNOPQRSTUVWXYZ_","kis");
				dict_type = strip(scan(col1,3,','));
				output &dict_list;

			end;
			if scan(col1,1,',')="B" then do;
				col_id = scan(col1,2,',');
				col_name = compress(tranwrd(trim(upcase(strip(scan(col1,2,';')))),' ','_'),"ABCDEFGHIJKLMNOPQRSTUVWXYZ_","kis");
				output &col_list;

			end;

	run;

%mend get_col_list;


%macro create_tables();


/* Create dictionaries definition */

	data dicts;
		set dict.full_data;
		length table_name $20;
		retain table_name;
		length col_id $20;
		length col_name $32;


			if prefix = "TD" then do;
				table_name=scan(col1,2,',');
			end;
			if prefix = "TP" then do;
				col_id = strip(scan(scan(col1,1,';'),2,','));
				col_name = substr(compress(tranwrd(trim(upcase(strip(scan(col1,2,';')))),' ','_'),"ABCDEFGHIJKLMNOPQRSTUVWXYZ_","kis"),1,32);
				keep table_name col_id col_name relation;
				output dicts;
			end;

	run;



	/* Lista zmian nazw kolumn */


	data _null_;
		set dict.dict_list;
		temp = "ewid." || compress(dict_id);
		tab_name = upcase(compress(dict_id));
		tab = compress(temp); ;


	/* Create table*/
		call execute(
			'data ' !! tab !! ';
				length col_name $300;
				set work.dicts;
				where table_name = "' !! dict_id !! '";
				keep col_id col_name;
			run;'
		);

	/* Transpose dataset */
		call execute(
			'proc transpose data = ' !! tab !! ' out = ' !! tab !! '(drop=_NAME_);
				id col_id;
				var col_name;
			run;'
		);

	/* Add extra columns */
		call execute(
			'data ' !! tab !! ';
				set ' !! tab !! ';
				length RECORD_ID $20;
				RECORD_ID = "";
			run;'
		);

	/* Clear datasets */
/*		call execute(*/
/*			'proc sql;*/
/*				delete from ' !! tab !!  ';*/
/*			run;'*/
/*		);*/


	run;


%mend create_tables;





%macro values_to_add();


	data dict.values_to_output(keep=table_name output_line)
		&relation_list(keep=relation_table relation_table_id relation_id foreign_table foreign_row)
		;
		set dict.full_data(
			keep=col1 prefix
			where= (prefix in ("RO","RD","RP","RC","D","WG","X"))
			);

		length table_name $20;
		retain table_name;
		length record_id $20;
		retain record_id;
		length output_line $500;
		retain output_line;
		length relation_id $10;
		length relation_name $100;


		if 	prefix in ("RO" ,"RD","RP","RC")
			then do;
				table_name = "ewid." || scan(col1,2,',');
				record_id = scan(col1,3,',');
				output_line = "RECORD_ID = ";
				output_line = strip(output_line) || '"' || strip(record_id) || '"; ';
		end;


		if prefix="WG" then do;
				relation_table = table_name;
				relation_table_id = record_id;

				relation_id = scan(col1,2,',');
				foreign_table = strip(scan(col1,3,','));
				foreign_row =scan(scan(col1,1,';'),4,',');
				output &relation_list;
		end;


			if prefix = "D" then do;
					line = compress(scan(col1,4,','),'"','i');
					column_name  = scan(col1,2,',');

					output_line = strip(output_line) || strip(column_name) || '="' || strip(line) || '"; ';
			end;

			if
				prefix = "X" and
				table_name ne ''
			then do;
			output dict.values_to_output;



			end;
	run;



%mend values_to_add;


%macro prepare_to_insert();
		proc sort data=dict.values_to_output out=tables_unique (keep=table_name) nodupkey;
			by table_name;
		run;

/*		proc sql;*/
/*			select distinct scan(table_name,2,'.') into :table_list separated by " " */
/*				from tables_unique;*/
/*		run;*/


		data tables_unique;
			set tables_unique;
				tab = compress(scan(table_name,2,'.'));
				call execute('data ' !! compress(tab) !! ';
					set dict.values_to_output;
					where table_name = "' !! table_name !! '";
					run;'
					);
		run;

		data temp;
			set tables_unique;
			call execute(cats('%input_data(',tab,')'));
		run;

	%macro input_data(tab_name);
		data _null_;
			set &tab_name;
					call execute(
		                      '
		                          data ' !! table_name !! '(drop = output_line);
		                                 set ' !! table_name !! 'end = eof;
										 output;
		                                 if eof = 1 then do;
		                                      '!! output_line !!'
		                                      output;
		                                 end;


		                           run;
		                      '
		                      );
		run;
	%mend input_data();

%mend prepare_to_insert;




%macro rename_cols(table_name);


	proc sort data=dicts ;
		by  table_name col_name;
	run;

	data temp(keep=table_name col_id col_name);
		set dicts;
		length col $32;
		retain col;
		if _N_ ne 1 then
		if col = col_name then col_name = compress(substr(col,1,31)||'1');
	output;
		col = col_name;
	run;


	data temp(keep=table_name line);
		set work.temp;
		line = strip(trim(col_id) || '=' || trim(col_name));
	run;

	proc sort data=temp ;
		by table_name;
	run;


	data temp2;
		set temp;
			by table_name;

			length rename_list $500;
			retain rename_list " ";

			if first.table_name then rename_list = trim(line);
			else
			rename_list = trim(line) || " " || rename_list;

			if last.table_name then do;
				call execute(
				'proc datasets library = ewid nolist;
					modify ' !!  table_name !! ';
					rename ' !! rename_list !! ';
				quit;'
				);
				output;
			end;

	run;



%mend rename_cols;



%macro create_geodata();
	data ewid.geo_data(keep=table_name row_id p g );
		set full_data(
			keep=col1
			where=(scan(col1,1,',') in ('RO','RD','RP','RC','P')));

		length table_name $30;
		retain table_name;
		length row_id $30;
		retain row_id;

		if
			scan(col1,1,',') = "RO" or
			scan(col1,1,',') = "RD" or
			scan(col1,1,',') = "RP" or
			scan(col1,1,',') = "RC"
		then do;
			table_name = scan(col1,2,',');
			row_id = scan(col1,3,',');
		end;

		if scan(col1,1,',') = "P" and scan(col1,2,',') = "G" then do;
			p = scan(col1,3,',');
			g = scan(col1,4,',');
			output;
		end;

	run;

%mend create_geodata;



%getdata
%get_col_list
%create_tables
%insert_values
%create_geodata



















	data part_data(keep=col1 prefix encoding="utf-8" compress=yes);
		infile "C:\Users\splszw\Documents\EWID\maly plik swd i raporty wygenerowane\226101_1_0005_005konw.swd"
	    	firstobs = 2
			missover
			flowover
	    	DSD
	    	lrecl = 32767;
		format col1 $500.;

		input ;

		col1 = _infile_;
		temp = scan(col1,1,',');
		temp2 = scan(col1,1,';');

/*		if temp = "TD"*/
/*			then flag = 1;*/

		if temp in ("B","TD","TP","RO","RD","RP","RC","D","P","WG","W")
		then do;
			prefix = temp;
			output;
		end;

		else if temp2 in("X","GL","GX")
		then do;
			prefix = temp2;
			output;
		end;


	run;


	data values_to_output(keep=table_name output_line)
		;
		set part_data(
			keep=col1 prefix
			where= (prefix in ("RO","RD","RP","RC","D","WG","X"))
			);

		length table_name $20;
		retain table_name;
		length record_id $20;
		retain record_id;
		length output_line $500;
		retain output_line;
		length relation_id $10;
		length relation_name $100;


		if 	prefix in ("RO" ,"RD","RP","RC")
			then do;
				table_name = "ewid." || scan(col1,2,',');
				record_id = scan(col1,3,',');
				output_line = "RECORD_ID = ";
				output_line = strip(output_line) || '"' || strip(record_id) || '"; ';
		end;


			if prefix = "D" then do;
					line = compress(scan(col1,4,','),'"','i');
					column_name  = scan(col1,2,',');

					output_line = strip(output_line) || strip(column_name) || '="' || strip(line) || '"; ';
			end;

			if
				prefix = "X" and
				table_name ne ''
			then do;
			output values_to_output;


			end;
/*		end;*/
	run;




		proc sort data=values_to_output out=tables_unique (keep=table_name) nodupkey;
			by table_name;
		run;

		proc sql;
			select distinct scan(table_name,2,'.') into :table_list separated by " "
				from tables_unique;
		run;


		data tables_unique;
		set tables_unique;
		tab = compress(scan(table_name,2,'.'));
		call execute('data ' !! compress(tab) !! ';
			set values_to_output;
			where table_name = "' !! table_name !! '";
			run;'
			);
		run;

		data temp;
			set tables_unique;
			call execute(cats('%input_data(',tab,')'));
		run;


	%macro input_data(tab_name);
		data _null_;
			set &tab_name;
					call execute(
		                      '
		                          data ' !! table_name !! '(drop = output_line);
		                                 set ' !! table_name !! 'end = eof;
										 output;
		                                 if eof = 1 then do;
		                                      '!! output_line !!'
		                                      output;
		                                 end;


		                           run;
		                      '
		                      );
		run;
	%mend input_data();